package cn.pconline.autoclub.service;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.TreeMap;

import org.apache.commons.lang.StringUtils;
import org.apache.velocity.VelocityContext;
import org.gelivable.dao.GeliDao;
import org.gelivable.dao.GeliOrm;
import org.gelivable.dao.SqlBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import cn.pconline.autoclub.config.SystemConfig;
import cn.pconline.autoclub.entity.Activity;
import cn.pconline.autoclub.entity.Album;
import cn.pconline.autoclub.entity.AwardLimit;
import cn.pconline.autoclub.entity.Club;
import cn.pconline.autoclub.entity.ClubAddress;
import cn.pconline.autoclub.entity.ClubDeleteLog;
import cn.pconline.autoclub.entity.ClubDispose;
import cn.pconline.autoclub.entity.ClubFocusImg;
import cn.pconline.autoclub.entity.ClubForum;
import cn.pconline.autoclub.entity.ClubIntegral;
import cn.pconline.autoclub.entity.ClubLevel;
import cn.pconline.autoclub.entity.ClubMember;
import cn.pconline.autoclub.entity.ClubNotice;
import cn.pconline.autoclub.entity.ClubPetrol;
import cn.pconline.autoclub.entity.ClubPetrolDaily;
import cn.pconline.autoclub.entity.ClubStat;
import cn.pconline.autoclub.entity.ClubStyle;
import cn.pconline.autoclub.entity.ClubTotalMember;
import cn.pconline.autoclub.entity.EntityWithClubProp;
import cn.pconline.autoclub.entity.MemberApplication;
import cn.pconline.autoclub.entity.Theme;
import cn.pconline.autoclub.entity.User;
import cn.pconline.autoclub.util.AppUtils;
import cn.pconline.autoclub.util.DateUtils;
import cn.pconline.autoclub.util.EncodeUtils;
import cn.pconline.autoclub.util.FileUtils;
import cn.pconline.autoclub.util.Pager;
import cn.pconline.autoclub.util.Regex;
import cn.pconline.autoclub.util.RegionUtil;
import cn.pconline.autoclub.util.SearchUrl;
import cn.pconline.autoclub.util.StringUtil;
import cn.pconline.autoclub.util.TmplUtils;
import cn.pconline.autoclub.util.dyna.AbstractDyna;
import cn.pconline.autoclub.util.mail.MailSenderInfo;
import cn.pconline.autoclub.util.mail.SimpleMailSender;
import cn.pconline.r.client.SimpleHttpTemplate;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.danga.MemCached.MemCachedClient;

/**
 *
 * @author chensy
 */
public class ClubService {

    @Autowired
    private GeliOrm geliOrm;
    @Autowired
    private GeliDao geliDao;
    @Autowired
    private MemberService memberService;
    @Autowired
    private AlbumService albumService;
    @Autowired
    private MessageService messageService;
    @Autowired
    private SimpleHttpTemplate simpleHttpTemplate;
    @Autowired
    private SystemConfig systemConfig;
    @Autowired
    private PetrolService petrolService;
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    private PagerService pagerService;
    @Autowired
    private ClubLevelService clubLevelService;
    @Autowired
    private ActivityService activityService;
    @Autowired
    private CommentService commentService;
    @Autowired
    private TopicService topicService;
    @Autowired
    private UserService userService;
    @Autowired
    private StatService statService;
    @Autowired
    private MemCachedClient mcc;
    @Autowired
    private ThemeService themeService;
    @Autowired
    private DynaService dynaService;
    @Autowired
    private ClubStyleService ClubStyleService;
    public static final int SORT_COUNTRY_TYPE = 1;//全国排名类型
    public static final int SORT_SERIES_TYPE = 2;//车第排名类型
    public static final int SORT_REGION_TYPE = 3;//地区排名类型
    private static final Logger log = LoggerFactory.getLogger(ClubService.class);
    private static List<String> likeNameKey = null;
    private static List<String> companyNameKey = null;
    private static List<String> brandNameKey = null;
    private static List<String> serialNameKey = null;
    private static List<String> serialDomainKey = null;
    private static List<String> brandDomainKey = null;
    private static List<String> specialDomainKey = null;

    /**
     * 只读一次文件
     */
    private void initKeyFromFile() {
        if (likeNameKey == null) {
            likeNameKey = FileUtils.readTextFile(systemConfig.getDataPath() + File.separator + "likeName.txt");
        }
        if (companyNameKey == null) {
            companyNameKey = FileUtils.readTextFile(systemConfig.getDataPath() + File.separator + "companyName.txt");
        }
        if (brandNameKey == null) {
            brandNameKey = FileUtils.readTextFile(systemConfig.getDataPath() + File.separator + "brandName.txt");
        }
        if (serialNameKey == null) {
            serialNameKey = FileUtils.readTextFile(systemConfig.getDataPath() + File.separator + "serialName.txt");
        }
        if (serialDomainKey == null) {
            serialDomainKey = FileUtils.readTextFile(systemConfig.getDataPath() + File.separator + "serialDomain.txt");
        }
        if (brandDomainKey == null) {
            brandDomainKey = FileUtils.readTextFile(systemConfig.getDataPath() + File.separator + "brandDomain.txt");
        }
        if (specialDomainKey == null) {
            specialDomainKey = FileUtils.readTextFile(systemConfig.getDataPath() + File.separator + "specialDomain.txt");
        }
    }

    /**
     *
     * @param clubId 如果clubId小于0，则返回fmt格式化字符串
     * @return
     */
    public String getClubIndexUrl(long clubId) {
        String fmt = systemConfig.getRoot() + "/%s/";
        if (clubId < 0) {
            return fmt;
        }
        return String.format(fmt, clubId);
    }

    /**
     * 优先返回自定义URL，否则返回带ID的URL
     *
     * @param clubId 如果clubUrl为空且clubId小于0，则返回fmt格式化字符串
     * @return
     */
    public String getClubIndexUrl(long clubId, String clubUrl) {
        String fmt = systemConfig.getRoot() + "/%s/";
        if (clubUrl == null || clubUrl.isEmpty()) {
            if (clubId < 0) {
                return fmt;
            } else {
                return String.format(fmt, clubId);
            }
        }
        return String.format(fmt, clubUrl);
    }

    /**
     * 创建车友会
     *
     * @param club
     * @param address
     * @return
     */
    public long create(Club club, ClubAddress address) {
        long clubId = geliDao.create(club);
        if (clubId > 0) {
            address.setClubId(clubId);
            geliDao.create(address);
            ClubPetrol petrol = new ClubPetrol();
            petrol.setClubId(clubId);
            petrol.setTotalPetrol(0);
            petrol.setUpdateAt(club.getCreateAt());
            geliDao.create(petrol);
        }
        return clubId;
    }

    /**
     * 更新（所有字段）
     *
     * @author fxr
     * @param o
     * @return
     */
    @Transactional(propagation = Propagation.REQUIRED, readOnly = false)
    public long update(Club o) {
        return geliDao.update(o);
    }

    /**
     * 车友会升级等级
     *
     * @param o
     * @return
     */
    @Transactional(propagation = Propagation.REQUIRED, readOnly = false)
    public long updateLevel(Club o) {
        return geliDao.update(o, "level_id");
    }

    /**
     * 判断车友会名称是否存在
     *
     * @param name
     * @return
     */
    public boolean isExist(String name) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select count(1) from ").appendSql(geliOrm.getTableName(Club.class));
        sql.appendSql(" where name = ").appendValue(name);
        long num = geliDao.count(sql.getSql(), sql.getValues());
        return num > 0;
    }

    /**
     * 通过ID获取状态为审核通过或被锁的车友会，不存在时，返回null
     *
     * @param clubId
     * @return
     */
    public Club getValidClub(long clubId) {
        if (clubId < 1) {
            return null;
        }
        Club club = getClub(clubId);
        if (club != null && (club.getStatus() == Club.STATUS_PASS
                || club.getStatus() == Club.STATUS_LOCK
                || club.getStatus() == Club.STATUS_SEALED)) {
            return club;
        }
        return null;
    }

    /**
     * 通过ID获取状态为审核通过的车友会，不存在时，返回null
     *
     * @param clubId
     * @return
     */
    public Club getNormalClub(long clubId) {
        Club club = getClub(clubId);
        if (club != null && club.getStatus() == Club.STATUS_PASS) {
            return club;
        }
        return null;
    }

    /**
     * 通过ID获取车友会，不存在时，返回null 一般操作建议调用 getValidClub方法
     *
     * @param clubId
     * @return
     */
    public Club getClub(long clubId) {
        try {
            if (clubId < 1) {
                return null;
            }
            return geliDao.find(Club.class, clubId);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 通过车友会ID，获取 已奖励认证油值
     *
     * @param clubId
     * @return
     */
    public AwardLimit getAwardLimit(long clubId, long clubIntegralId) {
        try {
            return geliDao.findFirst(AwardLimit.class, "select * from ac_award_limit where club_id = ? and club_integral_id = ?", clubId, clubIntegralId);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 修改车友会基本信息，目前只更新简介
     *
     * @param clubId
     * @param introduce 车友会简介
     * @param adminId 更新信息的管理员Id
     * @return
     */
    public Club saveInfo(long clubId, String introduce, String qqGroup, long adminId) {
        Club club = getClub(clubId);
        club.setIntroduce(introduce);
        club.setQqGroup(qqGroup);
        club.setUpdateAt(DateUtils.getNow());
        club.setUpdateBy(adminId);
        geliDao.update(club);
        return club;
    }

    /**
     * 更新车标
     *
     * @param clubId
     * @param logo 车标的URL
     * @param adminId 更新信息的管理员Id
     * @return
     */
    public Club saveLogo(long clubId, String logo, long adminId) {
        Club club = getClub(clubId);
        club.setLogo(logo);
        club.setUpdateAt(DateUtils.getNow());
        club.setUpdateBy(adminId);
        geliDao.update(club);
        return club;
    }

    /**
     * 最多只拿三张图片
     *
     * @param clubId
     * @return
     */
    public List<ClubFocusImg> getFocusImg(long clubId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select focus_img_id from ").appendSql(geliOrm.getTableName(ClubFocusImg.class));
        sql.appendSql(" where club_id = ").appendValue(clubId).appendSql(" order by seq ");
        try {
            return geliDao.page(ClubFocusImg.class, sql.getSql(), 1, 3, sql.getValues());
        } catch (DataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 更新焦点图片
     *
     * @param focusImgId
     * @param clubId
     * @param adminId
     * @param current
     * @param title
     * @param url
     * @param imgUrl
     * @return
     */
    private ClubFocusImg saveFocusImg(long focusImgId, long clubId, long adminId, Date current,
            String title, String url, String imgUrl, int seq) {
        ClubFocusImg img = new ClubFocusImg();
        img.setClubId(clubId);
        if (StringUtils.isEmpty(imgUrl)) {//删除焦点图
            imgUrl = "";
            title = "";
            url = "";
        }
        img.setImgUrl(imgUrl);
        img.setSeq(seq);
        img.setTitle(title);
        img.setUpdateAt(current);
        img.setUpdateBy(adminId);
        img.setUrl(url);
        if (focusImgId == 0) {
            focusImgId = geliDao.create(img);
            img.setFocusImgId(focusImgId);
        } else {
            img.setFocusImgId(focusImgId);
            geliDao.update(img);
        }
        return img;
    }

    /**
     * 更新车友会的三张焦点图片 如果不足三张或者一张都没有，则插入三条空记录
     *
     * @param clubId
     * @param adminId
     * @param title1
     * @param url1
     * @param imgUrl1
     * @param title2
     * @param url2
     * @param imgUrl2
     * @param title3
     * @param url3
     * @param imgUrl3
     * @return
     */
    public List<ClubFocusImg> saveFocusImg(long clubId, long adminId,
            String title1, String url1, String imgUrl1,
            String title2, String url2, String imgUrl2,
            String title3, String url3, String imgUrl3) {
        List<ClubFocusImg> list = getFocusImg(clubId);
        Date current = DateUtils.getNow();
        long id1 = 0;
        long id2 = 0;
        long id3 = 0;
        if (!list.isEmpty()) {
            id1 = list.get(0).getFocusImgId();
            id2 = list.get(1).getFocusImgId();
            id3 = list.get(2).getFocusImgId();
        }
        saveFocusImg(id1, clubId, adminId, current, title1, url1, imgUrl1, 1);
        saveFocusImg(id2, clubId, adminId, current, title2, url2, imgUrl2, 2);
        saveFocusImg(id3, clubId, adminId, current, title3, url3, imgUrl3, 3);
        return list;
    }

    /**
     * 更新车友会的三张焦点图片 如果不足三张或者一张都没有，则插入三条空记录
     *
     * @param clubId
     * @param adminId
     * @param title1
     * @param url1
     * @param imgUrl1
     * @param title2
     * @param url2
     * @param imgUrl2
     * @param title3
     * @param url3
     * @param imgUrl3
     * @return
     */
    public List<ClubFocusImg> saveFocusImg(long clubId, long adminId,
            String title1, String url1, String imgUrl1, int img1Seq,
            String title2, String url2, String imgUrl2, int img2Seq,
            String title3, String url3, String imgUrl3, int img3Seq) {
        List<ClubFocusImg> list = getFocusImg(clubId);
        Date current = DateUtils.getNow();
        long id1 = 0;
        long id2 = 0;
        long id3 = 0;
        if (!list.isEmpty()) {
            id1 = list.get(0).getFocusImgId();
            id2 = list.get(1).getFocusImgId();
            id3 = list.get(2).getFocusImgId();
        }
        saveFocusImg(id1, clubId, adminId, current, title1, url1, imgUrl1, img1Seq);
        saveFocusImg(id2, clubId, adminId, current, title2, url2, imgUrl2, img2Seq);
        saveFocusImg(id3, clubId, adminId, current, title3, url3, imgUrl3, img3Seq);
        return list;
    }

    /**
     * 获取车友会的收货地址
     *
     * @param clubId
     * @return
     */
    public ClubAddress getAddress(long clubId) {
        ClubAddress clubAddress = null;
        try {
            clubAddress = geliDao.find(ClubAddress.class, clubId);
        } catch (DataAccessException e) {
        }
        return clubAddress;
    }

    /**
     * 获取处理信息
     *
     * @param clubId
     * @return
     */
    public ClubDispose getClubDispose(long clubId) {
        ClubDispose dispose = null;
        try {
            dispose = geliDao.find(ClubDispose.class, clubId);
        } catch (DataAccessException e) {
        }
        return dispose;
    }

    /**
     * 判断公告是否已经存在
     *
     * @param clubId
     * @return
     */
    public boolean isNoticeExist(long clubId) {
        return geliDao.exists(ClubNotice.class, clubId);
    }

    /**
     * 获取车友会的公告
     *
     * @param clubId
     * @return
     */
    public ClubNotice getNotice(long clubId) {
        try {
            return geliDao.find(ClubNotice.class, clubId);
        } catch (DataAccessException e) {
            ClubNotice notice = new ClubNotice();
            notice.setClubId(clubId);
            notice.setContent("");
            notice.setStatus(ClubNotice.STATUS_NOT_AUDITED);
            notice.setUpdateAt(DateUtils.getNow());
            notice.setUpdateBy(clubId);
            geliDao.create(notice);
            return notice;
        }
    }

    /**
     * 更新车友会公告
     *
     * @param clubId
     * @param content
     * @param adminId 更新公告的管理员ID
     * @return
     */
    public ClubNotice saveNotice(long clubId, String content, long adminId) {
        ClubNotice notice = getNotice(clubId);
        notice.setContent(content);
        notice.setUpdateAt(DateUtils.getNow());
        notice.setUpdateBy(adminId);
        geliDao.update(notice);
        return notice;
    }

    /**
     * 更新车友会收货地址
     *
     * @param clubId
     * @param address
     * @param adminId 更新地址的管理员ID
     * @return
     */
    public ClubAddress saveAddress(long clubId, ClubAddress address, long adminId) {
        address.setClubId(clubId);
        address.setUpdateAt(DateUtils.getNow());
        address.setUpdateBy(adminId);
        geliDao.update(address);
        return address;
    }

    /**
     * 去BBS那获取车系和地区的相关论坛
     *
     * @param club
     */
    public void addClubForum(Club club) {
        String relUrl = systemConfig.getRoot() + "/ClubService.audit.addClubForum";
        String seriesUrl = systemConfig.getBbsRoot() + "/intf/getForumByCarSeries.do?";
        seriesUrl += "brandId=" + club.getBrandId();
        seriesUrl += "&seriesId=" + club.getSeriesId();
        String cityUrl = systemConfig.getBbsRoot() + "/intf/getForumByArea.do?";
        cityUrl += "cityId=" + club.getCityId();
        cityUrl += "&provinceId=" + club.getProvinceId();
        JSONObject seriesRs = JSONObject.parseObject(simpleHttpTemplate.get(seriesUrl, relUrl));
        JSONObject cityRs = JSONObject.parseObject(simpleHttpTemplate.get(cityUrl, relUrl));
        if (seriesRs == null || cityRs == null) {
            log.error("addClubForum fail!maybe network problum!");
            log.error("seriesUrl:" + seriesUrl);
            log.error("cityUrl:" + cityUrl);
            return;
        }
        JSONObject seriesForum = seriesRs.getJSONObject("seriesForum");
        JSONObject brandForum = seriesRs.getJSONObject("brandForum");
        JSONObject cityForum = cityRs.getJSONObject("cityForum");
        JSONObject provinceForum = cityRs.getJSONObject("provinceForum");
        int seq = 1;
        Date curDate = DateUtils.getNow();
        if (seriesForum != null) {
            ClubForum f = new ClubForum();
            f.setForumId(seriesForum.getLongValue("fid"));
            f.setClubId(club.getClubId());
            f.setPopulation(seriesForum.getLongValue("postCount"));
            f.setSeq(seq++);
            f.setTitle(seriesForum.getString("forumName"));
            f.setUpdateAt(curDate);
            f.setUpdateBy(club.getCreateBy());
            f.setUrl(seriesForum.getString("forumUrl"));
            f.setIcon(seriesForum.getString("icon"));
            f.setType(ClubForum.TYPE_SERIAL);
            creatteClubForum(f);
        } else if (brandForum != null) {
            ClubForum f = new ClubForum();
            f.setForumId(brandForum.getLongValue("fid"));
            f.setClubId(club.getClubId());
            f.setPopulation(brandForum.getLongValue("postCount"));
            f.setSeq(seq++);
            f.setTitle(brandForum.getString("forumName"));
            f.setUpdateAt(curDate);
            f.setUpdateBy(club.getCreateBy());
            f.setUrl(brandForum.getString("forumUrl"));
            f.setIcon(brandForum.getString("icon"));
            f.setType(ClubForum.TYPE_SERIAL);
            creatteClubForum(f);
        }
        if (cityForum != null) {
            ClubForum f = new ClubForum();
            f.setForumId(cityForum.getLongValue("fid"));
            f.setClubId(club.getClubId());
            f.setPopulation(cityForum.getLongValue("postCount"));
            f.setSeq(seq++);
            f.setTitle(cityForum.getString("forumName"));
            f.setUpdateAt(curDate);
            f.setUpdateBy(club.getCreateBy());
            f.setUrl(cityForum.getString("forumUrl"));
            f.setIcon(cityForum.getString("icon"));
            f.setType(ClubForum.TYPE_AREA);
            creatteClubForum(f);
        } else if (provinceForum != null) {
            ClubForum f = new ClubForum();
            f.setForumId(provinceForum.getLongValue("fid"));
            f.setClubId(club.getClubId());
            f.setPopulation(provinceForum.getLongValue("postCount"));
            f.setSeq(seq++);
            f.setTitle(provinceForum.getString("forumName"));
            f.setUpdateAt(curDate);
            f.setUpdateBy(club.getCreateBy());
            f.setUrl(provinceForum.getString("forumUrl"));
            f.setIcon(provinceForum.getString("icon"));
            f.setType(ClubForum.TYPE_AREA);
            creatteClubForum(f);
        }
        if (club.getThemeId() > 0) {
            Theme t = themeService.findThemeById(club.getThemeId());
            if (t != null) {
                ClubForum f = new ClubForum();
                f.setForumId(t.getForumId());
                f.setClubId(club.getClubId());
                f.setPopulation(0);
                f.setSeq(seq++);
                f.setTitle(t.getThemeName());
                f.setUpdateAt(curDate);
                f.setUpdateBy(club.getCreateBy());
                f.setUrl(systemConfig.getBbsRoot() + "/forum-" + t.getForumId() + ".html");
                f.setIcon(t.getIcon());
                f.setType(ClubForum.TYPE_THEME);
                creatteClubForum(f);
            }
        }
    }

    private void creatteClubForum(ClubForum clubForum) {
        long id = geliDao.generateId(ClubForum.class);
        clubForum.setClubForumId(id);
        String sql = geliOrm.getCreateSql(clubForum);
        String orgTbl = geliOrm.getTableName(clubForum.getClass());
        sql = sql.replace(sql.substring(0, sql.indexOf(orgTbl)), "insert ignore into ");
        Map<String, Object> params = geliOrm.map(clubForum);
        log.info("club forum sql: {}, params: {}", sql, params);
        int insertCnt = geliDao.getNamedParameterJdbcTemplate().update(sql, params);
        if (insertCnt == 0) {
            geliDao.deleteCache(clubForum.getClass(), clubForum.getClubForumId());
            log.info("the club[{}] was already bind forum[{}].", clubForum.getClubId(), clubForum.getForumId());
        }
    }

    /**
     * 管理员对车友会进行审核，通过车友会|只能未审核和被拒绝的才能通过
     *
     * @param clubId
     * @param auditBy
     * @return
     */
    public boolean passClub(Club club, long auditBy) {
        User user = geliDao.find(User.class, club.getCreateBy());
        String msg = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        if (club.getStatus() != Club.STATUS_REJECTED && club.getStatus() != Club.STATUS_UNAUDITED) {
            return false;
        }
        int limit = userService.updateJoinedClubCount(user);
        limit += memberService.findMemberApplication(user.getUid(), 0l, true).size();
        limit += this.editFounder(user.getUid(), club.getClubId()).size();
        if (limit >= Integer.parseInt(systemConfig.getJoinClubLimit())) {
            return false;
        }
        messageService.send(user, MessageService.PASS_CLUB, msg);
        //因为是由未审和拒绝变成审核状态，所以目前车友会是没有会长、没有论坛、没有相册的
        //设置相关论坛
        addClubForum(club);
        //创建设置会长
        memberService.createMembership(user, club.getClubId(), ClubMember.ADMIN);
        //创建默认相册
        albumService.createAlbum(club.getClubId(), 0, "默认相册", Album.ISDEFAULT_YES); // 车友会默认相册创建者0，只有管理员才能编辑
        //创建车友会通过同步会长的精华帖
        memberService.loadPickByUserIdClubId(club.getClubId(), user.getUid());
        String updateSql = "audit_at,audit_by,status";
        club.setAuditAt(DateUtils.getNow());
        club.setAuditBy(auditBy);
        club.setStatus(Club.STATUS_PASS);
        geliDao.update(club, updateSql);
        //审核通过发动态
        AbstractDyna dyna = new AbstractDyna();
        dyna.setClubId(club.getClubId());
        dyna.setContent("");//内容固定，前台拼凑就行了
        dyna.setCreateBy(club.getCreateBy());
        dyna.setCreateDate(club.getCreateAt());
        dyna.setCreateName(user.getNickname());
        dyna.setTargetId(0);
        dynaService.createCreateClubDyna(dyna, null);
        //审核通过创建一条默认套装记录
        ClubStyle clubStyle = new ClubStyle();
        clubStyle.setClubId(club.getClubId());
        clubStyle.setCreateAt(club.getCreateAt());
        clubStyle.setCreateBy(club.getCreateBy());
        ClubStyleService.createDefaultClubStyle(clubStyle);


        //发短信通知会长
        messageService.sendSMS(user.getPhone(), user.getUid(), MessageService.PASS_CLUB_SMS, club.getName());

        if (user.getEmail() != null && user.getEmail().matches(Regex.email.value)) {
            //发邮件通知会长
            String[] mailToAddress = {user.getEmail()};
            VelocityContext velocityContext = new VelocityContext();
            velocityContext.put("nickName", user.getNickname());
            velocityContext.put("clubName", club.getName());
            velocityContext.put("clubUrl", club.getClubUrl());
            velocityContext.put("ucRoot", systemConfig.getUcRoot());
            velocityContext.put("userId", user.getUid());
            velocityContext.put("root", systemConfig.getRoot());
            String subject = String.format(MessageService.PASS_CLUB_MAIL_SUBJECT, club.getName());
            String content = TmplUtils.render(systemConfig.getDataPath() + File.separator, "clubPassMailTempate.html", velocityContext);
            MailSenderInfo mailInfo = SimpleMailSender.getSenderInfo(systemConfig.getMailServerHost(), systemConfig.getMailServerPort(),
                    true, systemConfig.getMailPassWord(), systemConfig.getFromAddress(), mailToAddress, subject, content);
            if (SimpleMailSender.sendHtmlMail(mailInfo)) {
                log.info("success to send mail for user[{}]", user.getUid());
            } else {
                log.error("fail success to send mail for user[{}]", user.getUid());
            }
        }
        return true;
    }

    /**
     * 管理员对车友会进行审核，解封车友会
     *
     * @param clubId
     * @param auditBy
     * @return
     */
    public boolean unsealClub(Club club, long auditBy) {
        if (club.getStatus() != Club.STATUS_SEALED) {
            return false;
        }
        String time = DateUtils.formatDetail(DateUtils.getNow());
        String msg = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        String passMsg = String.format(MessageService.UNSEAL_CLUB, msg, time);
        String updateSql = "audit_at,audit_by,status";
        club.setAuditAt(DateUtils.getNow());
        club.setAuditBy(auditBy);
        club.setStatus(Club.STATUS_PASS);
        geliDao.update(club, updateSql);
        List<ClubMember> members = memberService.listMemberByClubId(club.getClubId());
        List<User> users = memberService.getUserByMember(members);
        messageService.sendMsg(users, passMsg);
        return true;
    }

    /**
     * 管理员对车友会进行审核，封车友会
     *
     * @param clubId
     * @param auditBy
     * @return
     */
    public boolean sealClub(Club club, long auditBy) {
        if (club.getStatus() != Club.STATUS_PASS) {
            return false;
        }
        String time = DateUtils.formatDetail(DateUtils.getNow());
        String msg = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        String passMsg = String.format(MessageService.SEAL_CLUB, msg, time);
        String updateSql = "audit_at,audit_by,status";
        club.setAuditAt(DateUtils.getNow());
        club.setAuditBy(auditBy);
        club.setStatus(Club.STATUS_SEALED);
        geliDao.update(club, updateSql);
        List<ClubMember> members = memberService.listMemberByClubId(club.getClubId());
        List<User> users = memberService.getUserByMember(members);
        messageService.sendMsg(users, passMsg);
        return true;
    }

    /**
     * 管理员对车友会进行审核，锁定车友会（取消通过）|只能未审核的车友会才能拒绝
     *
     * @param clubId
     * @param auditBy
     * @param rejectReason
     * @return
     */
    public boolean rejectClub(Club club, long auditBy, String rejectReason) {
        if (club.getStatus() != Club.STATUS_UNAUDITED) {
            return false;
        }
        User user = geliDao.find(User.class, club.getCreateBy());
        String updateSql = "audit_at,audit_by,status";
        String rMsg = String.format(MessageService.REFUSED_CLUB, club.getName(), MessageService.REFUSED_CLUB_DEF_REASON);
        if (StringUtils.isNotBlank(rejectReason)) {
            rMsg = String.format(MessageService.REFUSED_CLUB, club.getName(), rejectReason);
            club.setRejectMsg(rMsg);//保存自定义的拒绝理由
            updateSql += ",reject_msg";
        }
        messageService.send(user, rMsg);
        club.setAuditAt(DateUtils.getNow());
        club.setAuditBy(auditBy);
        club.setStatus(Club.STATUS_REJECTED);
        geliDao.update(club, updateSql);
        return true;
    }

    /**
     * 管理员对车友会进行审核，解锁车友会（重新通过）|只有被锁的车友会才能被解
     *
     * @param clubId
     * @param auditBy
     * @return
     */
    public boolean unlockClub(Club club, long auditBy) {
        if (club.getStatus() != Club.STATUS_LOCK) {
            return false;
        }
        String time = DateUtils.formatDetail(DateUtils.getNow());
        String msg = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        String passMsg = String.format(MessageService.UNLOCK_CLUB, msg, time);
        String updateSql = "audit_at,audit_by,status";
        club.setAuditAt(DateUtils.getNow());
        club.setAuditBy(auditBy);
        club.setStatus(Club.STATUS_PASS);
        geliDao.update(club, updateSql);
        List<ClubMember> members = memberService.listMemberByClubId(club.getClubId());
        List<User> users = memberService.getUserByMember(members);
        messageService.sendMsg(users, passMsg);
        return true;
    }

    /**
     * 管理员对车友会进行审核，锁定车友会（取消通过）|只有通过的车友会才能被锁
     *
     * @param clubId
     * @param auditBy
     * @return
     */
    public boolean lockClub(Club club, long auditBy) {
        if (club.getStatus() != Club.STATUS_PASS) {
            return false;
        }
        String time = DateUtils.formatDetail(DateUtils.getNow());
        String msg = messageService.assembleClubUrl(club.getClubUrl(), club.getName());
        String passMsg = String.format(MessageService.LOCK_CLUB, msg, time);
        String updateSql = "audit_at,audit_by,status";
        club.setAuditAt(DateUtils.getNow());
        club.setAuditBy(auditBy);
        club.setStatus(Club.STATUS_LOCK);
        geliDao.update(club, updateSql);
        List<ClubMember> members = memberService.listMemberByClubId(club.getClubId());
        List<User> users = memberService.getUserByMember(members);
        messageService.sendMsg(users, passMsg);
        return true;
    }

    /**
     * 后台审核车友会
     *
     * @param uid
     * @param b
     * @return
     */
    private List<Club> editFounder(long uid, long clubId) {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT club_id FROM ac_club WHERE admin_id=").appendValue(uid);
        sql.appendSql(" AND club_id <> ").appendValue(clubId);
//    	sql.appendSql(" AND status <> ").appendValue(Club.STATUS_REJECTED);
        List<Club> list = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        return list;
    }

    /**
     * 获取相关论坛列表
     *
     * @param clubId
     * @return
     */
    public List<ClubForum> getForumList(long clubId) {
        SqlBuilder sql = new SqlBuilder();
        //这里用select * 就查询不到记录 好奇怪，其他地方用select * 又可以，迟点再看下原因
        sql.appendSql("select club_forum_id from ").appendSql(geliOrm.getTableName(ClubForum.class));
        sql.appendSql(" where club_id = ").appendValue(clubId);
        sql.appendSql(" order by seq");
        try {
            return geliDao.list(ClubForum.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据车友会名查找车友会，如果查找不到返回NULL
     *
     * @param name 车友会名
     * @return
     */
    public Club findByName(String name) {
        SqlBuilder sqlBuilder = new SqlBuilder();
        sqlBuilder.appendSql("SELECT club_id FROM ac_club WHERE name = ").appendValue(name);
        List<Club> list = geliDao.list(Club.class, sqlBuilder.getSql(), sqlBuilder.getValues());
        return list.isEmpty() ? null : list.get(0);
    }

    /**
     * 根据创建者查找车友会
     *
     * @param uid 创建者uid
     * @param unauditedOnly 是否只查未审核的
     * @return
     */
    public List<Club> findByFounder(long uid, boolean unauditedOnly) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT club_id FROM ac_club WHERE admin_id=").appendValue(uid);
        if (unauditedOnly) {
            sql.appendSql(" AND (status=0 or status = 2)");
        }
        List<Club> list = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        return list;
    }

    /**
     * 批量预读车友会成员信息中的用户信息
     *
     * @param entities 一批车友会成员
     * @return 填充用户信息之后的
     */
    @SuppressWarnings("rawtypes")
    public List loadTransientClubs(List<? extends EntityWithClubProp> entities) {
        if (entities == null || entities.isEmpty()) {
            return entities;
        }
        long[] ids = new long[entities.size()];
        for (int i = 0; i < entities.size(); i++) {
            EntityWithClubProp entity = entities.get(i);
            if (entity != null) {
                ids[i] = entity.getClubId();
            }
        }
        List<Club> clubs = geliDao.list(Club.class, ids);
        for (int i = 0; i < entities.size(); i++) {
            EntityWithClubProp entity = entities.get(i);
            if (entity != null) {
                entity.setClub(clubs.get(i));
            }
        }
        return entities;
    }

    /**
     * 设置实时油值（用于方便页面显示，不操作数据库）
     *
     * @param club
     * @return
     */
    public Club setPetrol(Club club) {
        ClubPetrol clubPetrol = geliDao.find(ClubPetrol.class, club.getClubId());
        club.setPetrol(clubPetrol);
        club.setTotalPetrol(clubPetrol.getTotalPetrol());
        return club;
    }

    /**
     * 判断vip人数的变化是否达到加减积分的条件
     *
     * @param vipNum 人数
     */
    private ClubIntegral getVipNumIntegralWhenAdd(int vipNum) {
        int itemId = Integer.MIN_VALUE;
        int newVipNum = vipNum + 1;
        switch (newVipNum) {
            case 5:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_5;
                break;
            case 10:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_10;
                break;
            case 20:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_20;
                break;
            case 30:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_30;
                break;
            case 40:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_40;
                break;
            case 50:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_50;
                break;
            case 100:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_100;
                break;
        }
        return petrolService.getById(itemId);
    }

    private ClubIntegral getVipNumIntegralWhenMinus(int vipNum) {
        int itemId = Integer.MIN_VALUE;
        int newVipNum = vipNum - 1;
        switch (newVipNum) {
            case 4:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_5;
                break;
            case 9:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_10;
                break;
            case 19:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_20;
                break;
            case 29:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_30;
                break;
            case 39:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_40;
                break;
            case 49:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_50;
                break;
            case 99:
                itemId = PetrolService.ADD_CLUB_PETROL_ITEM_VIP_NUM_100;
                break;
        }
        ClubIntegral i = petrolService.getById(itemId);
        i.setPetrol(-i.getPetrol());
        return i;
    }

    /**
     * 实时校验vip人数
     *
     * @param clubId
     * @return
     */
    public void synVipNum(Club club) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT count(1) FROM ac_member_info i,ac_user u WHERE i.user_id = u.user_id AND i.club_id = ");
        sql.appendValue(club.getClubId());
        sql.appendSql(" AND u.vip = 1");
        try {
            int num = geliDao.count(sql.getSql(), sql.getValues());
            if (club.getVipNum() != num) {
                String rs = String.format("club[%s]'s vipNum not consistency! Set vipNum %s,but vipNum is %s actually!Now has syn!", club.getClubId(), club.getVipNum(), num);
                log.error(rs);
                club.setVipNum(num);
                geliDao.update(club, "vip_num");
            }
        } catch (EmptyResultDataAccessException e) {
        }
    }

    /**
     * 实时校验会员人数
     *
     * @param clubId
     * @return
     */
    private void synMemberNum(Club club) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select count(1) from ").appendSql(geliOrm.getTableName(ClubMember.class));
        sql.appendSql(" where club_id = ").appendValue(club.getClubId());
        int num = 0;
        try {
            num = geliDao.count(sql.getSql(), sql.getValues());
        } catch (EmptyResultDataAccessException e) {
        }
        if (club.getMemberNum() != num) {
            String rs = String.format("club[%s]'s memberNum not consistency! Set memberNum %s,but memberNum is %s actually!Now has syn!", club.getClubId(), club.getMemberNum(), num);
            log.error(rs);
            club.setMemberNum(num);
            geliDao.update(club, "member_num");
        }
    }

    /**
     *
     * VIP人数增加 不只是简单的加减操作，还会select count，校验一次
     *
     * @param clubId
     */
    public void increaseVipNum(long clubId) {
        synchronized (this) {
            Club club = getClub(clubId);
            ClubIntegral i = getVipNumIntegralWhenAdd(club.getVipNum());
            club.setVipNum(club.getVipNum() + 1);
            geliDao.update(club, "vip_num");

            if (i != null) {
                AwardLimit award = this.getAwardLimit(clubId, i.getIntegralId()); // 认证会员奖励油值 -- 不为空表示已加过油值
                if (award == null) {
                    award = new AwardLimit();
                    award.setClubId(clubId);
                    award.setClubIntegralId(i.getIntegralId());
                    geliDao.create(award);

                    //vip认证人数直接给车友会加减分
                    petrolService.addToClubWithinLimit(clubId, i.getIntegralId(), i.getPetrol(), i.getName(), false);
                }
            }
            synVipNum(club);
        }
    }

    /**
     * VIP人数减少 不只是简单的加减操作，还会select count，校验一次
     *
     * @param clubId
     */
    public void decreaseVipNum(long clubId) {
        synchronized (this) {
            Club club = getClub(clubId);
            //这时返回的油值要求是负数来的
//            ClubIntegral i = getVipNumIntegralWhenMinus(club.getVipNum());
            club.setVipNum(club.getVipNum() - 1);
            geliDao.update(club, "vip_num");
//            if (i != null) {
//                //取消认证人数直接给车友会减分
//                petrolService.addToClubWithinLimit(clubId, i.getIntegralId(), i.getPetrol(), i.getName(), false);
//            }
            synVipNum(club);
        }
    }

    /**
     * 会员人数增加 不只是简单的加减操作，还会select count，校验一次
     *
     * @param clubId
     */
    public void increaseMemberNum(long clubId) {
        synchronized (this) {
            Club club = getClub(clubId);
            club.setMemberNum(club.getMemberNum() + 1);
            geliDao.update(club, "member_num");
            synMemberNum(club);
        }
    }

    /**
     * 会员人数减少 不只是简单的加减操作，还会select count，校验一次
     *
     * @param clubId
     */
    public void decreaseMemberNum(long clubId) {
        synchronized (this) {
            Club club = getClub(clubId);
            club.setMemberNum(club.getMemberNum() - 1);
            geliDao.update(club, "member_num");
            synMemberNum(club);
        }
    }

    /**
     * 车友会等级升级
     *
     * @param clubId
     */
    public Map<Boolean, String> updateClubLevel(long clubId, long levelId) {
        Map<Boolean, String> info = new HashMap<Boolean, String>();
        // 车友会
        Club club = getNormalClub(clubId);
        if (club == null) {
            log.error("升级的车友会不存在");
            info.put(false, "升级的车友会不存在");
            return info;
        }
        if (!clubLevelService.isLeagle(levelId)) {
            log.error("升级状态有误");
            info.put(false, "升级状态有误");
            return info;
        }
        if (club.getLevelId() == levelId) {
            info.put(false, "已经是该级别");
            return info;
        }
        // 车友会油值
        ClubPetrol petrol = petrolService.getValidPetrol(clubId);
        if (petrol == null) {
            log.error("升级的车友会油值表不存在!");
            info.put(false, "升级的车友会油值表不存在!");
            return info;
        }

        // 车友会等级
        ClubLevel currLevel = clubLevelService.getById(club.getLevelId());
        // 如果为最后一个等级，不会调用该接口，所以直接加一取下一等级
        ClubLevel nextLevel = clubLevelService.getNextLevel(currLevel.getLevelId());
        if (nextLevel == null) {
            info.put(false, "级别有误!");
            return info;
        }
        if (nextLevel.getLevelId() != levelId) {
            info.put(false, "请一级一级升!");
            return info;
        }
        // 比较
        if (nextLevel.getLevelPetrol() <= petrol.getTotalPetrol()
                && nextLevel.getLevelMember() <= club.getVipNum()) {
            // 升级
            club.setLevelId(nextLevel.getLevelId());
            updateLevel(club);
            info.put(true, "<p>恭喜你！车友会 <span class=\"blue\">" + club.getName() + "</span> 升级为" + nextLevel.getLevelName() + "！</p><p><a href=\"javascript:levelPrizes()\" class=\"btn\">领取福利</a></p>");
            return info;
        }

        log.error("油值或认证人数未达下一等级要求，无法升级!");
        info.put(false, "油值或认证人数未达下一等级要求，无法升级!");
        return info;
    }

    /**
     * 车友会降级操作
     *
     * @param clubId
     * @param isDown
     */
    public void downClubLevel(long clubId, int isDown) {
        Club club = this.getNormalClub(clubId);
        if (club == null || clubLevelService.isLeast(club.getLevelId())) {
            return;
        }
        ClubLevel curLevel = clubLevelService.getPreLevel(club.getLevelId());
        if (curLevel == null) {
            return;
        }
        club.setLevelId(curLevel.getLevelId());
        this.update(club);
        //发送消息
        clubLevelService.sendClubDownLevel(club, curLevel, isDown);
    }

    /**
     * 根据焦点图ID删除指定焦点图 注：这里的删除指数据清空，不是真正的删除
     *
     * @param focusId
     * @return
     */
    public boolean deleteFocusImgById(long focusId) {

        boolean result = false;
        ClubFocusImg focusImg = geliDao.find(ClubFocusImg.class, focusId);
        focusImg.setImgUrl("");
        focusImg.setTitle("");
        focusImg.setUrl("");
        focusImg.setSeq(ClubFocusImg.EMPTY_SEQ);
        if (geliDao.update(focusImg) > 0) {
            result = true;
        }
        return result;
    }

    /**
     * 根据域名地址判断记录是否已存在
     *
     * @param url
     * @return
     */
    public Club getClubByUrl(String url) {
        if (StringUtils.isBlank(url)) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select * from ").appendSql(geliOrm.getTableName(Club.class));
        sql.appendSql(" where club_url = ").appendValue(url.trim());
        try {
            return geliDao.findFirst(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException ex) {
            return null;
        }
    }

    /**
     * 根据域名查询车友会ID
     *
     * @param url
     * @return
     */
    public long findClubIdByUrl(String url) {
        if (StringUtils.isBlank(url)) {
            return 0;
        }
        String key = "ClubUrl-" + url;
        Long id = (Long) mcc.get(key);
        if (id != null && id > 0) {
            return id;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select club_id from ").appendSql(geliOrm.getTableName(Club.class));
        sql.appendSql(" where club_url = ").appendValue(url);
        try {
            id = (long)jdbcTemplate.queryForInt(sql.getSql(), sql.getValues());
            mcc.add(key, id);
            return id;
        } catch (EmptyResultDataAccessException e) {
            return 0;
        }
    }

    /**
     * 根据用户ID，拿出所有的车友会ID
     *
     * @param userId
     * @return
     */
    public List<Club> listByUserId(long userId, int status) {
        if (userId < 1) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT a.club_id FROM ac_club a ,ac_member_info b WHERE a.club_id = b.club_id  ");
        sql.appendSql(" AND b.user_id =  ").appendValue(userId);
        if (status != -99) {
            sql.appendSql(" AND a.status =").appendValue(status);
        }
        List<Club> list = null;
        try {
            list = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException e) {

            log.error(" can't find my joined club that user_id is " + userId);
        }
        return list;
    }

    /**
     * 根据用户ID，拿出所有加入的车友会ID
     *
     * @param userId
     * @return
     */
    public List<Club> listByUserId(long userId) {
        if (userId < 1) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club ,ac_member_info WHERE ac_club.club_id = ac_member_info.club_id  ");
        sql.appendSql(" AND ac_member_info.user_id =  ").appendValue(userId);
        setAllowFindStatus(sql);
        List<Club> list = null;
        try {
            list = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException e) {

            log.error(" can't find my joined club that user_id is " + userId);
        }
        return list;
    }

    /**
     * 根据区、省、品牌、车系查询车友会
     *
     * @param region
     * @param province
     * @param brand
     * @param series
     * @return
     */
    public Pager<Club> pageClubByPlace(String region, String province, String brand, String series, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id ");
        sql.appendSql(" where 1=1 ");
        if (StringUtils.isNotBlank(region)) {
            sql.appendSql(" and ac_club.region_name = ").appendValue(region);
        }
        if (StringUtils.isNotBlank(province)) {
            sql.appendSql(" and ac_club.province_name = ").appendValue(province);
        }
        if (StringUtils.isNotBlank(brand)) {
            sql.appendSql(" and ac_club.brand_name = ").appendValue(brand);
        }
        if (StringUtils.isNotBlank(series)) {
            sql.appendSql(" and ac_club.series_name = ").appendValue(series);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_petrol.total_petrol desc ");
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 获取全国上周认证人数排行榜，取前num个数
     *
     * @param num
     * @return
     */
    public List<ClubTotalMember> listNumOfClub(int num) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select * from ac_club_total_member order by (week_total - lastweek_total) desc ");
        if (num > 0) {
            sql.appendSql(" limit 0," + num);
        }
        List<ClubTotalMember> list = null;
        try {
            list = geliDao.list(ClubTotalMember.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException e) {
            log.warn("no clubs!", e);
        }
        if (list != null && !list.isEmpty()) {
            for (ClubTotalMember clubSum : list) {
                clubSum.setClub(this.getClub(clubSum.getClubId()));
            }
        }
        return list;
    }

    /**
     * 根据地区ID获取车友会列表
     *
     * @param regionId
     * @param cityId
     * @param idList 需要排除掉的车友会ID集合
     * @return
     */
    public List<Club> listClubByRegion(long cityId, long provinceId, long areaId, int topNum, List<Long> idList) {
        if (provinceId < 0 && cityId < 0 && areaId < 0) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id = ").appendValue(provinceId);
        }
        if (cityId > 0) {
            sql.appendSql(" and ac_club.city_id = ").appendValue(cityId);
        }
        if (areaId > 0) {
            sql.appendSql(" and ac_club.region_id = ").appendValue(areaId);
        }

        if (idList != null && !idList.isEmpty()) {
            sql.appendSql(" and ac_club.club_id not in ").appendValues(idList.toArray());
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");
        List<Club> list = null;
        try {
            list = geliDao.page(Club.class, sql.getSql(), 1, topNum, sql.getValues());
        } catch (DataAccessException e) {
            log.warn("no clubs!", e);
        }
        return list;
    }

    /**
     * 根据地区ID获取车友会列表
     *
     * @param regionId
     * @param cityId
     * @param idList 需要排除掉的车友会ID集合
     * @return
     */
    public List<Club> listClubByRegion(long cityId, long provinceId, long areaId, List<Long> idList) {
        if (provinceId < 1 && cityId < 1 && areaId < 1) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id = ").appendValue(provinceId);
        }
        if (cityId > 0) {
            sql.appendSql(" and ac_club.city_id = ").appendValue(cityId);
        }
        if (areaId > 0) {
            sql.appendSql(" and ac_club.region_id = ").appendValue(areaId);
        }
        if (idList != null && !idList.isEmpty()) {
            sql.appendSql(" and ac_club.club_id not in (");
            int len = idList.size();
            for (int i = 0; i < len; i++) {
                sql.appendValue(idList.get(i));
                if (i > 0 && i < len - 1) {
                    sql.appendSql(",");
                }
            }
            sql.appendSql(") ");
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");
        List<Club> list = null;
        try {

            list = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException e) {
            log.warn("no clubs!", e);
        }
        return list;
    }

    /**
     * 根据区ID、省ID、品牌ID、车系ID查询车友会
     *
     * @param region
     * @param province
     * @param brand
     * @param series
     * @param sids（不要传车系id为0的进来）
     * @return
     */
    public Pager<Club> pageClubByPlaceId(long cityId, long regionId, long provinceId, long brandId, Object[] sids, long clubId, String clubName, int pageNo, int pageSize, int tabId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (cityId > 0) {
            sql.appendSql(" and ac_club.city_id =").appendValue(cityId);
        }
        if (regionId > 0) {
            sql.appendSql(" and ac_club.region_id =").appendValue(regionId);
        }
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id =").appendValue(provinceId);
        }
        if (brandId > 0) {
            sql.appendSql(" and ac_club.brand_id =").appendValue(brandId);
        }
        if (sids != null && sids.length > 0) {
            sql.appendSql(" and ac_club.series_id in ").appendValues(sids);
        }
        if (clubId > 0) {
            sql.appendSql(" and ac_club.club_id =").appendValue(clubId);
        }
        if (StringUtils.isNotBlank(clubName)) {
            sql.appendSql(" and ac_club.name like ").appendValue("%" + clubName + "%");
        }
        if (tabId > 0) {//增加主题条件
            sql.appendSql(" and ac_club.theme_id = ").appendValue(tabId);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc , ac_club.total_petrol desc, ac_club.create_at  ");
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 增加主题参数tagId,对原调用此方法功能不受影响
     *
     */
    public Pager<Club> pageClubByPlaceId(long cityId, long regionId, long provinceId, long brandId, Object[] sids, long clubId, String clubName, int pageNo, int pageSize) {
        return pageClubByPlaceId(cityId, regionId, provinceId, brandId, sids, clubId, clubName, pageNo, pageSize, 0);
    }

    /**
     * 根据论坛ID获取有效车友会列表
     *
     * @param forumId
     * @return
     */
    public Pager<Club> pagerClubByForumId(long forumId, int pageNo, int pageSize) {
        if (forumId < 1) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club ,ac_club_forum WHERE ac_club.club_id  = ac_club_forum.club_id ");
        sql.appendSql(" AND ac_club_forum.forum_id =").appendValue(forumId);
        sql.appendSql(" ORDER BY ac_club.total_petrol DESC ");
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 根据论坛ID获取车友会个数
     *
     * @param forumId
     * @return
     */
    public int countClubByForumId(long forumId) {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT count(1) FROM ac_club ,ac_club_forum WHERE ac_club.club_id  = ac_club_forum.club_id ");
        sql.appendSql(" AND ac_club_forum.forum_id =").appendValue(forumId);
        return geliDao.count(sql.getSql(), sql.getValues());
    }

    /**
     * wap版筛选
     *
     * @param pn 省份名
     * @param cn 城市名
     * @param bid 品牌id
     * @param sid 系列id
     * @return
     */
    public List<Club> select(String pn, String cn, long bid, long sid, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (!"".equals(pn)) {
            sql.appendSql(" and ac_club.province_name like ");
            sql.appendValue("%" + pn + "%");
        }
        if (!"".equals(cn)) {
            sql.appendSql(" and ac_club.city_name like ");
            sql.appendValue("%" + cn + "%");
        }
        if (bid > 0) {
            sql.appendSql(" and ac_club.brand_id = ");
            sql.appendValue(bid);
        }
        if (sid > 0) {
            sql.appendSql(" and ac_club.series_id = ");
            sql.appendValue(sid);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc , ac_club.total_petrol desc, ac_club.create_at  ");
        sql.appendSql("limit ").appendValue(pageNo * pageSize).appendSql(",").appendValue(pageSize);
        try {
            return geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException e) {
            return new ArrayList<Club>();
        }
    }

    /**
     * 根据省份和城市查找车友会
     *
     * @param cityId
     * @param provinceId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Club> listClubByCityId(long cityId, long provinceId, int pageNo, int pageSize) {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id WHERE 1=1 ");
        if (cityId > 0) {
            sql.appendSql("and ac_club.city_id=").appendValue(cityId);
        } else if (provinceId > 0) {
            sql.appendSql("and ac_club.province_id=").appendValue(provinceId);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");

        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 根据省份和城市查找车友会(返回List)
     *
     * @param cityId
     * @param provinceId
     * @param topNo前几名同城车友会
     * @return
     */
    public List<Club> listClubByCityId(long cityId, long provinceId, int limit) {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id WHERE 1=1 ");
        if (cityId > 0) {
            sql.appendSql(" and ac_club.city_id=").appendValue(cityId);
        } else if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id=").appendValue(provinceId);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at");

        if (limit > 0) {
            sql.appendSql(" LIMIT " + limit);
        }

        return geliDao.list(Club.class, sql.getSql(), sql.getValues());
    }

    /**
     * 根据省份和城市查找车友会(返回Pager)
     *
     * @param cityId
     * @param provinceId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Club> pageClubByCityId(long cityId, long provinceId, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id WHERE 1=1 ");
        sql.appendSql(" and ac_club.city_id=").appendValue(cityId);
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id=").appendValue(provinceId);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at");

        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 根据省份和城市查找星级车友会
     *
     * @param cityId
     * @param provinceId
     * @param pageNo
     * @param pageSize
     * @param level
     * @return
     */
    public Pager<Club> listClubByCityId(long cityId, long provinceId, int pageNo, int pageSize, int level) {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id WHERE 1=1 ");
        if (cityId > 0) {
            sql.appendSql("and ac_club.city_id=").appendValue(cityId);
        } else if (provinceId > 0) {
            sql.appendSql("and ac_club.province_id=").appendValue(provinceId);
        }
        if (level > 0) {
            sql.appendSql(" and ac_club.level_id>=").appendValue(level);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");

        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 取出参与排行榜的车友会，更新缓存
     *
     * @return
     */
    public List<Club> initCacheClub() {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT club_id FROM ac_club WHERE level_id >0");
        //设置允许查找车友会状态
        return geliDao.list(Club.class, sql.getSql(), sql.getValues());
    }

    /**
     * 根据地区id查询数据库，车友会前端专用
     *
     * @param regionId
     * @param numLimit
     * @return
     */
    public List<Club> waplistClubByRegion(long regionId, int numLimit) {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1 = 1 ");
        if (regionId > 0) {
            if (regionId == 8) {
                //港澳台--目前不支持台湾
                sql.appendSql(" and ac_club.province_id in (810000,820000,710000)");
            } else {
                sql.appendSql(" and ac_club.region_id =").appendValue(regionId);
                sql.appendSql(" and ac_club.province_id not in (810000,820000,710000)");
            }

        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");
        if (numLimit > 0) {
            sql.appendSql(" LIMIT " + numLimit);
        }
        List<Club> list = null;
        try {
            list = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException e) {
            log.warn("no clubs in the region[{}]", regionId);
        }
        return list;
    }

    /**
     * 根据车友会ID删除地址记录，》》》车友会ID主键
     *
     * @param clubId
     */
    public void deleteAddressByClubId(long clubId) {
        if (clubId < 1) {
            return;
        }
        ClubAddress address = geliDao.find(ClubAddress.class, clubId);
        if (address != null) {
            geliDao.delete(address, clubId);
        }
    }

    /**
     * 根据车友会ID删除已拒绝的数据
     *
     * @param adminId
     * @param club
     * @return
     */
    public Map<Object, String> removeClub(long clubId, long adminId) {
        Map<Object, String> map = new HashMap<Object, String>();
        Club club = geliDao.find(Club.class, clubId);
        if (club.getStatus() != Club.STATUS_REJECTED) {
            map.put(false, "该车友会不是已拒绝状态");
        } else {
            //删除车友会表记录
            geliDao.delete(club, clubId);
            //车友会车友会油值记录
            petrolService.deletePetrolByClubId(clubId);
            //删除车友会地址记录
            this.deleteAddressByClubId(clubId);
            map.put(true, "删除成功");
        }
        return map;
    }

    /**
     * 根据车友会ID删除级联论坛
     *
     * @param clubId
     * @return
     */
    public int deleteForumByClubId(long clubId) {
        if (clubId < 1) {
            return 0;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" delete from ").appendSql(geliOrm.getTableName(ClubForum.class));
        sql.appendSql(" where club_id = ?");
        return jdbcTemplate.update(sql.getSql(), clubId);
    }

    public List<ClubPetrolDaily> initClubDailyStat() {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select petrol_daily_id from ").appendSql(geliOrm.getTableName(ClubPetrolDaily.class));
        return geliDao.list(ClubPetrolDaily.class, sql.getSql());
    }

    /**
     * 获取用户跟车友会关系
     *
     * @param userId
     * @param clubId
     * @return 0 普通会员 1副会长，2 会长 -1 游客 -2 已经加入其他车友会或申请加入其他车友会的游客 -3 审核中
     */
    public int getClubRelationByUser(User user, long clubId) {
        if (user == null || user.getUid() == 0) {
            return -1;
        }
        Club club = getClub(clubId);
        if (club != null) {
            ClubMember clubMember = memberService.findMember(user.getUid(), clubId);
            if (clubMember != null) {
                return clubMember.getAdminType();
            }
            MemberApplication memberApplication = memberService.getMemberApplication(clubId, user.getUid(), MemberApplication.STATUS_UNAUDITED);
            if (memberApplication != null) {
                return -3;
            }
        }
        List<Club> listClub = this.findByFounder(user.getUid(), true);
        if (listClub != null && listClub.size() > 0) {
            return -2;
        }
        List<ClubMember> clubMembers = memberService.listClubMemberByUser(user.getUid());
        if (clubMembers != null && clubMembers.size() > 0) {
            return -2;
        }
        List<MemberApplication> memberApplications = memberService.listMemberApplicationsByUserId(user.getUid());
        if (memberApplications != null && memberApplications.size() > 0) {
            return -2;
        }
        return -1;
    }

    /**
     * 根据论坛ID删除车友会-论坛级联关系，当只存在一个关系时，不能删除
     *
     * @param memberId
     * @return 1>成功 0》失败 2》只有一个论坛，不能删除
     */
    public long removeForumById(long clubForumId, long clubId) {

        int result = 0;
        List<ClubForum> listForum = getForumList(clubId);
        if (listForum != null && !listForum.isEmpty()) {
            if (listForum.size() == 1) {
                result = 2;
            } else {
                for (ClubForum forum : listForum) {
                    if (forum.getClubForumId() == clubForumId) {
                        if (geliDao.delete(forum, clubForumId) > 0) {
                            //Club club = this.getClub(clubId);
                            result = 1;
                        }
                    }
                }
            }
        }
        return result;
    }

    private void setAllowFindStatus(SqlBuilder sql) {
        sql.appendSql(" and ( ac_club.status =").appendValue(Club.STATUS_PASS).appendSql(" or ac_club.status>").appendValue(Club.STATUS_REJECTED).appendSql(" )");
    }

    /**
     * 默认获取第一个论坛ID
     *
     * @param clubId
     * @return
     */
    public long getFirstFormum(long clubId) {
        long result = 0;
        if (clubId < 1) {
            return result;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select * from ").appendSql(geliOrm.getTableName(ClubForum.class));
        sql.appendSql(" where seq = 1 and club_id = ").appendValue(clubId);
        ClubForum forum = geliDao.findFirst(ClubForum.class, sql.getSql(), sql.getValues());
        if (forum != null) {
            result = forum.getForumId();
        }
        return result;
    }

    /**
     * 车友会对象信息转json
     *
     * @param club
     * @param sourceAgent
     * @return
     */
    public JSONObject setTopClub2Json(Club club, int sourceAgent) {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("clubId", club.getClubId());
        jsonObject.put("clubStatus", club.getStatus());
        jsonObject.put("name", EncodeUtils.encodeForHTML(club.getName(), sourceAgent));
        jsonObject.put("logo", EncodeUtils.encodeForHTML(club.getLogo(), sourceAgent));
        jsonObject.put("memberSum", club.getMemberNum());
        jsonObject.put("totalPetrol", petrolService.getValidPetrol(club.getClubId()).getTotalPetrol());
        jsonObject.put("level", club.getLevelId());
        jsonObject.put("levelName", EncodeUtils.encodeForHTML(clubLevelService.getById(club.getLevelId()).getLevelName(), sourceAgent));
        String activityName = "";
        List<Activity> listActivity = activityService.getNewActivity(club.getClubId(), 1);
        if (listActivity != null && !listActivity.isEmpty()) {
            Activity activity = listActivity.get(0);
            activityName = activity.getTitle();
        }
        jsonObject.put("activityName", EncodeUtils.encodeForHTML(activityName, sourceAgent));
        jsonObject.put("commentSum", commentService.countByClubId(club.getClubId()));
        jsonObject.put("regionRank", club.getRegionRank());
        jsonObject.put("brandRank", club.getBrandRank());
        jsonObject.put("provinceRank", club.getProvinceRank());
        jsonObject.put("rank", club.getRank());
        jsonObject.put("albumSum", albumService.countAlbumByClubId(club.getClubId()));
        jsonObject.put("photoSum", albumService.countPhotoByClubId(club.getClubId()));
        jsonObject.put("topicSum", topicService.countByClubId(club.getClubId()));
        jsonObject.put("activitySum", activityService.countActivityByClubId(club.getClubId()));
        jsonObject.put("seriesName", EncodeUtils.encodeForHTML(club.getSeriesName(), sourceAgent));
        jsonObject.put("provinceName", EncodeUtils.encodeForHTML(club.getProvinceName(), sourceAgent));
        jsonObject.put("cityName", EncodeUtils.encodeForHTML(club.getCityName(), sourceAgent));
        jsonObject.put("introduce", EncodeUtils.encodeForHTML(club.getIntroduce(), sourceAgent));
        jsonObject.put("announcement", EncodeUtils.encodeForHTML(this.getNotice(club.getClubId()).getContent(), sourceAgent));
        jsonObject.put("clubUrl", club.getClubUrl());
        jsonObject.put("url", systemConfig.getRoot() + "/" + club.getClubUrl() + "/");
        jsonObject.put("brandId", club.getBrandId());
        jsonObject.put("brandName", EncodeUtils.encodeForHTML(club.getBrandName(), sourceAgent));
        //主题URL
        jsonObject.put("clubBackgroundImg", club.getAppBanner() == null ? "" : club.getAppBanner());
        //判断是否有升级提示
        boolean isTopLevel = clubLevelService.isHighest(club.getLevelId());
        long nextNeedPetrol = 0;
        long nextNeedVip = 0;
        if (!isTopLevel) {
            ClubLevel clubNextLevel = clubLevelService.getNextLevel(club.getLevelId());
            if (clubNextLevel != null) {
                long nextLevelId = clubNextLevel.getLevelId();
                nextNeedPetrol = clubLevelService.getNextLevelNeedPetrol(nextLevelId, (int) club.getTotalPetrol());
                nextNeedVip = clubLevelService.getNextLevelNeedMember(nextLevelId, (int) club.getVipNum());
            }
        }
        if (!isTopLevel && nextNeedVip < 1 && nextNeedPetrol < 1) {
            jsonObject.put("levelupTip", 1);//提示可升级
        } else {
            jsonObject.put("levelupTip", 0);//提示不可升级
        }
        //获取等待审核加入车友会申请数量
        try {
            List<MemberApplication> listMember4Audit = memberService.listMemberApplications4Audit(club.getClubId());
            jsonObject.put("applyCount", listMember4Audit.size());
        } catch (EmptyResultDataAccessException e) {
            log.error("无法获取待审加入车友会成员信息clubId=" + club.getClubId());
        }
        //会长昵称
        ClubMember adminClubMember = memberService.findAdminByClubId(club.getClubId());
        jsonObject.put("presidentName", userService.getNickNameByUserId(adminClubMember.getUid()));
        return jsonObject;
    }

    /**
     * 根据用户获取加入车友会
     */
    public JSONArray getUserClubById(long uid, int sourceAgent) {
        JSONArray arr = new JSONArray();
        List<ClubMember> list = memberService.listClubMemberByUser(uid);
        if (list != null && !list.isEmpty()) {
            for (ClubMember cm : list) {
                Club club = this.getClub(cm.getClubId());
                if (club != null) {
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("name", EncodeUtils.encodeForHTML(club.getName(), sourceAgent));
                    jsonObject.put("clubId", club.getClubId());
                    jsonObject.put("logo", EncodeUtils.encodeForHTML(club.getLogo(), sourceAgent));
                    jsonObject.put("level", club.getLevelId());
                    jsonObject.put("regionRank", club.getRegionRank());
                    jsonObject.put("brandRank", club.getBrandRank());
                    jsonObject.put("provinceRank", club.getProvinceRank());
                    jsonObject.put("rank", club.getRank());
                    jsonObject.put("url", systemConfig.getRoot() + "/" + club.getClubUrl() + "/");
                    jsonObject.put("adminType", cm.getAdminType());
                    jsonObject.put("memberSum", club.getMemberNum());
                    jsonObject.put("regionId", club.getRegionId());
                    jsonObject.put("regionName", club.getRegionName());
                    jsonObject.put("provinceId", club.getProvinceId());
                    jsonObject.put("provinceName", club.getProvinceName());
                    jsonObject.put("cityId", club.getCityId());
                    jsonObject.put("cityName", club.getCityName());
                    jsonObject.put("seriesId", club.getSeriesId());
                    jsonObject.put("seriesName", club.getSeriesName());
                    arr.add(jsonObject);
                }
            }
        }
        return arr;
    }

    /**
     * 验证车友会名称
     *
     * @param name
     * @return
     */
    public String checkClubName(String name) {
        if (StringUtils.isBlank(name)) {
            return "请输入车友会名称";
        } else if (!checkKeyNameAndClub(name).equals("true")) {
            return checkKeyNameAndClub(name);
        } else if (this.isKeyName(name)) {
            return "该名称关键字不允许创建";
        } else if (name.matches(Regex.nameAdditional.value)) {
            return "不能使用纯数字作为车友会名称";
        } else if (this.isExist(name)) {
            return "车友会名称已经存在";
        } else if (!name.matches(Regex.chineseNumAbc.value)) {
            return "1-15个汉字或1-30个字符（字母、数字）";
        } else if (StringUtil.length(name) > 30) {
            return "1-15个汉字或1-30个字符（字母、数字）";
        } else {
            return "true";
        }
    }

    /**
     * 验证车友会名称(草稿专用，名称可以不写)
     *
     * @param name
     * @return
     */
    public String checkDraftName(String name) {
        if (name == null || StringUtils.isBlank(name)) {
            return "true";
        }
        if (!checkKeyNameAndClub(name).equals("true")) {
            return checkKeyNameAndClub(name);
        } else if (this.isKeyName(name)) {
            return "该名称关键字不允许创建";
        } else if (name.matches(Regex.nameAdditional.value)) {
            return "不能使用纯数字作为车友会名称";
        } else if (this.isExist(name)) {
            return "车友会名称已经存在";
        } else if (!name.matches(Regex.chineseNumAbc.value)) {
            return "1-15个汉字或1-30个字符（字母、数字）";
        } else if (StringUtil.length(name) > 30) {
            return "1-15个汉字或1-30个字符（字母、数字）";
        } else {
            return "true";
        }
    }

    /**
     *
     * 验证车友会QQ群号，可选的
     *
     * @param name
     * @return
     */
    public String checkClubQQGroup(String qqGroup) {
        if (qqGroup.length() > 30) {
            return "QQ群号长度过长";
        }
        if (StringUtils.isNotBlank(qqGroup)) {
            if (!qqGroup.matches(Regex.qqGroup.value)) {
                return "请输入正确的QQ群号";
            }
            if (isQQGroupExist(qqGroup)) {
                return "该QQ群号已被使用";
            }
        }
        return "true";
    }

    /**
     *
     * 验证车友会QQ群号，可选的
     *
     * @param name
     * @return
     */
    public String checkClubQQGroup(String qqGroup, long clubId) {
        if (qqGroup.length() > 30) {
            return "QQ群号长度过长";
        }
        if (StringUtils.isNotBlank(qqGroup)) {
            if (!qqGroup.matches(Regex.qqGroup.value)) {
                return "请输入正确的QQ群号";
            }
            Club club = this.findClubByQQgroup(qqGroup);
            if (club != null && club.getClubId() != clubId) {
                return "该QQ群号已被其它车友会使用";
            }
        }
        return "true";
    }

    /**
     * 校验车友会用户手机号
     *
     * @param phone
     * @return
     */
    public String checkClubPhone(String phone) {
        if (StringUtils.isBlank(phone)) {
            return "请填写手机";
        }
        if (!phone.matches(Regex.cellphone.value)
                || phone.matches(Regex.specialPhone.value)) {
            return "请填写合法的手机";
        }
        return "true";
    }

    /**
     * 验证车友会个性化域名
     *
     * @param url
     * @return
     */
    public String checkClubUrl(String url) {
        if (StringUtils.isBlank(url)) {
            return "个性域名不能为空";
        } else {
            if (this.isKeyUrl(url)) {
                return "该域名关键字不允许创建";
            } else if (url.trim().length() < 4 || url.trim().length() > 20
                    || !url.matches(Regex.letterornum.value)) {
                return "长度为4~20个字符的字母或数字+字母的组合";
            } else if (url.matches(Regex.nameAdditional.value)) {
                return "不能使用纯数字作为个性域名";
            } else if (this.getClubByUrl(url) != null) {
                return "您输入的域名已被占用，请更换其他域名";
            }
        }
        return "true";
    }

    /**
     * 验证车友会个性化域名(草稿专用，域名可以为空)
     *
     * @param url
     * @return
     */
    public String checkDraftUrl(String url) {
        if (url == null || StringUtils.isBlank(url)) {
            return "true";
        }
        if (this.isKeyUrl(url)) {
            return "该域名关键字不允许创建";
        } else if (url.trim().length() < 4 || url.trim().length() > 20
                || !url.matches(Regex.letterornum.value)) {
            return "长度为4~20个字符的字母或数字+字母的组合";
        } else if (url.matches(Regex.nameAdditional.value)) {
            return "不能使用纯数字作为个性域名";
        } else if (this.getClubByUrl(url) != null) {
            return "您输入的域名已被占用，请更换其他域名";
        }
        return "true";
    }

    /**
     * 判断个性化域名是不是关键字
     *
     * @param url
     * @return
     */
    public boolean isKeyUrl(String url) {
        initKeyFromFile();
        for (String key : brandDomainKey) {
            key = key.replaceAll(" ", "");//不含空格
            if (key.equalsIgnoreCase(url)) {//不区分大小写
                return true;
            }
        }
        for (String key : serialDomainKey) {
            key = key.replaceAll(" ", "");//不含空格
            if (key.equalsIgnoreCase(url)) {
                return true;
            }
        }
        for (String key : specialDomainKey) {
            key = key.replaceAll(" ", "");//不含空格
            if (key.equalsIgnoreCase(url)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 判断车友会名称是不是关键字
     *
     * @param name
     * @return
     */
    public boolean isKeyName(String name) {
        initKeyFromFile();
        for (String key : likeNameKey) {
            if (name.contains(key)) {
                return true;
            }
        }
        for (String company : companyNameKey) {
            company = company.replaceAll(" ", "");//去掉空格
            if (name.equalsIgnoreCase(company) || (company + "车友会").equalsIgnoreCase(name)) {
                return true;
            }
        }
        for (String brand : brandNameKey) {
            brand = brand.replaceAll(" ", "");//去掉空格
            if (name.equalsIgnoreCase(brand)) {
                return true;
            }
            for (String serial : serialNameKey) {
                serial = serial.replaceAll(" ", "").replaceAll("(进口)", "");//去掉空格、去掉进口
                serial = brand + serial + "车友会";
                if (name.equalsIgnoreCase(serial)) {
                    return true;
                }
            }
        }
        for (String serial : serialNameKey) {
            serial = serial.replaceAll(" ", "").replaceAll("(进口)", "");//去掉空格、去掉进口
            if (name.equalsIgnoreCase(serial)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 验证关键字是否为品牌+车友会或车系+车友会组合
     *
     * @param name
     * @return
     */
    public String checkKeyNameAndClub(String name) {
        initKeyFromFile();
        for (String brand : brandNameKey) {
            brand = brand.replaceAll(" ", "");//去掉空格
            if ((brand + "车友会").equalsIgnoreCase(name)) {
                return "品牌+车友会组合的关键字不允许被创建";
            }
        }
        for (String serial : serialNameKey) {
            serial = serial.replaceAll(" ", "").replaceAll("(进口)", "");//去掉空格、去掉进口
            if ((serial + "车友会").equalsIgnoreCase(name)) {
                return "车系+车友会组合的关键字不允许被创建";
            }
        }
        return "true";
    }

    public Map<String, Object> getAdminTypeAndIdByMember(ClubMember clubMember, List<MemberApplication> memberApplications, Club club) {
        Map<String, Object> map = new HashMap<String, Object>();
        if (clubMember != null) {
            if (clubMember.getClubId() == club.getClubId()) {
                map.put("memberId", clubMember.getId());
                map.put("adminType", clubMember.getAdminType());
                return map;
            }
            map.put("memberId", 0);
            map.put("adminType", AppUtils.STAUTS_JOINOTHER);
            return map;
        } else if (memberApplications != null && memberApplications.size() > 0) {
            for (MemberApplication m : memberApplications) {
                if (m.getClubId() == club.getClubId()) {
                    map.put("memberId", m.getId());
                    map.put("adminType", AppUtils.STATUS_UNAUDITED);
                    return map;
                }
            }
            map.put("memberId", 0);
            map.put("adminType", AppUtils.STAUTS_JOINOTHER);
        } else {
            map.put("memberId", 0);
            map.put("adminType", AppUtils.STATUS_SIGHTSEER);
        }
        return map;
    }

    /**
     * 根据状态分页查询车友会数据
     *
     * @param type 1>通过和锁 封的
     * @return
     */
    public Pager<Club> listClubByStatusForPage(int type, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        if (type == 1) {
            sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id ");
            sql.appendSql(" where  (ac_club.status = ").appendValue(Club.STATUS_PASS);
            sql.appendSql(" or ac_club.status = ").appendValue(Club.STATUS_LOCK)
                    .appendSql(" or ac_club.status = ").appendValue(Club.STATUS_SEALED).appendSql(")");
            sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");
        }
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 获取排名type:1》全国排名，2》品牌排名，3》区域排名
     *
     * @param type
     * @param pageNo
     * @param pageSize
     * @param brandId品牌ID
     * @param regionId区域ID
     * @param beginRank排名开始位置
     * @param endRank排名结束位置
     * @return
     */
    public List<Club> listClubBySortForList(int type, int pageNo, int pageSize, long brandId, long provinceId, int beginRank, int endRank) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT club_id FROM ac_club where 1=1 ");
        if (type == SORT_COUNTRY_TYPE) {
            if (beginRank > 0 && endRank > 0) {
                sql.appendSql(" and rank >= ").appendValue(beginRank);
                sql.appendSql(" and rank <= ").appendValue(endRank);
            }
            sql.appendSql(" and rank > 0 order by rank");
        } else if (type == SORT_SERIES_TYPE) {
            if (beginRank > 0 && endRank > 0) {
                sql.appendSql(" and brand_rank >= ").appendValue(beginRank);
                sql.appendSql(" and brand_rank <= ").appendValue(endRank);
            }
            sql.appendSql(" and brand_id = ").appendValue(brandId);
            sql.appendSql(" and brand_rank > 0 order by brand_rank");
        } else if (type == SORT_REGION_TYPE) {
            if (beginRank > 0 && endRank > 0) {
                sql.appendSql(" and province_rank >= ").appendValue(beginRank);
                sql.appendSql(" and province_rank <= ").appendValue(endRank);
            }
            sql.appendSql(" and province_id = ").appendValue(provinceId);
            sql.appendSql(" and province_rank > 0 order by province_rank");
        }
        return pagerService.pager(Club.class, sql, pageNo, pageSize).getResultList();

    }

    /**
     * 返回车友会的前后几名，根据活跃度进行排名
     *
     * @param beginRank
     * @param endRank
     * @return
     */
    public List<Club> listNearClubByCountory(int beginRank, int endRank) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT club_id FROM ac_club where 1=1 ");
        if (beginRank > 0 && endRank > 0) {
            sql.appendSql(" and rank >= ").appendValue(beginRank);
            sql.appendSql(" and rank <= ").appendValue(endRank);
        }
        sql.appendSql(" and rank > 0 order by rank");
        try {
            return geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException ex) {
            return Collections.emptyList();
        }
    }

    /**
     * 返回车友会的前后几名，根据活跃度进行排名 返回包括排名总油值信息
     *
     * @param beginRank
     * @param endRank
     * @return
     */
    public List<Map<String, Object>> listNearByClub(int beginRank, int endRank) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT c.club_id,c.rank,c.name,c.club_url,s.rank_petrol FROM ac_club c ");
        sql.appendSql(" LEFT JOIN ac_club_stat s ON c.club_id = s.club_id where 1=1 ");
        if (beginRank > 0 && endRank > 0) {
            sql.appendSql(" and c.rank >= ").appendValue(beginRank);
            sql.appendSql(" and c.rank <= ").appendValue(endRank);
        }
        sql.appendSql(" and c.rank > 0 order by c.rank");
        try {
            List<Map<String, Object>> clubList = jdbcTemplate.queryForList(sql.getSql(), sql.getValues());
            return clubList;
        } catch (DataAccessException ex) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据车友会地区分类
     *
     * @param sourceAgent
     * @param limit
     * @return
     */
    public JSONArray getClubListGroupByArea(int sourceAgent, int limit) {
        JSONArray arr = new JSONArray();
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id ");
        sql.appendSql(" where  (ac_club.status = ").appendValue(Club.STATUS_PASS);
        sql.appendSql(" or ac_club.status = ").appendValue(Club.STATUS_LOCK).appendSql(")");
        sql.appendSql(" and ac_club.province_id > 0 and ac_club.region_id > 0 ");
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");
        List<Club> listClub = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        Map<String, List<Club>> maps = new HashMap<String, List<Club>>();
        if (listClub != null) {
            for (Club club : listClub) {
                List<Club> list = maps.get(club.getRegionId() + "");
                if (list == null) {
                    list = new ArrayList<Club>();
                    maps.put(club.getRegionId() + "", list);
                }
                if (list.size() < limit) {
                    list.add(club);
                }
            }
        }
        if (maps.size() > 0) {
            //对maps的key指定顺序
            List<String> keys = new ArrayList<String>();
            for (long rid : RegionUtil.sortedRegionIds) {
                if (maps.containsKey(rid + "")) {
                    keys.add(rid + "");
                }
            }
            for (String key : keys) {
                JSONObject obj = new JSONObject();
                obj.put("areaname", RegionUtil.getRegionName(Long.parseLong(key)));
                obj.put("areaId", key);
                arr.add(jsonForCMS(obj, maps.get(key), sourceAgent));
            }
            if (listClub.size() < limit) {
                limit = listClub.size();
            }
            //加载全国数据
            JSONObject objArr = new JSONObject();
            objArr.put("areaname", "全国");
            objArr.put("areaId", "0");
            arr.add(0, jsonForCMS(objArr, listClub.subList(0, limit), sourceAgent));
        }
        return arr;
    }

    /**
     * 根据车友会品牌分类
     *
     * @param sourceAgent
     * @param limit
     * @return
     */
    public JSONArray getClubListGroupByBrand(int sourceAgent, int limit) {
        JSONArray arr = new JSONArray();
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id ");
        sql.appendSql(" where  (ac_club.status = ").appendValue(Club.STATUS_PASS);
        sql.appendSql(" or ac_club.status = ").appendValue(Club.STATUS_LOCK).appendSql(")");
        sql.appendSql(" and ac_club.brand_id > 0 ");
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");
        List<Club> listClub = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        Map<String, List<Club>> maps = new HashMap<String, List<Club>>();
        if (listClub != null) {
            for (Club club : listClub) {
                List<Club> list = maps.get(club.getBrandName());
                if (list == null) {
                    list = new ArrayList<Club>();
                    maps.put(club.getBrandName(), list);
                }
                if (list.size() < limit) {
                    list.add(club);
                }
            }
        }
        if (maps.size() > 0) {
            //对maps的key指定顺序
            List<String> keys = new ArrayList<String>();
            List<SearchUrl> sortedBrand = getBreadOrSeries(listClub, 0, 1);
            for (SearchUrl s : sortedBrand) {
                if (maps.containsKey(s.getBrandName())) {
                    keys.add(s.getBrandName());
                }
            }
            for (String key : keys) {
                JSONObject obj = new JSONObject();
                obj.put("brandName", key);
                List<Club> tList = maps.get(key);
                if (tList != null && !tList.isEmpty()) {
                    obj.put("brandId", tList.get(0).getBrandId());
                }
                arr.add(jsonForCMS(obj, maps.get(key), sourceAgent));
            }
        }
        return arr;
    }

    /**
     * cms前段车友会对象换json
     *
     * @param list
     * @return
     */
    public JSONObject jsonForCMS(JSONObject obj, List<Club> list, int sourceAgent) {
        if (list != null && !list.isEmpty()) {
            JSONArray clubArr = new JSONArray();
            for (Club club : list) {
                JSONObject clubObj = new JSONObject();
                clubObj.put("clubId", club.getClubId());
                clubObj.put("clubName", EncodeUtils.encodeForHTML(club.getName(), sourceAgent));
                clubObj.put("logo", club.getLogo());
                clubObj.put("provinceId", club.getProvinceId());
                clubObj.put("provinceName", EncodeUtils.encodeForHTML(club.getProvinceName(), sourceAgent));
                clubObj.put("cityId", club.getCityId());
                clubObj.put("cityName", EncodeUtils.encodeForHTML(club.getCityName(), sourceAgent));
                clubObj.put("brandId", club.getBrandId());
                clubObj.put("brandName", EncodeUtils.encodeForHTML(club.getBrandName(), sourceAgent));
                clubObj.put("seriesId", club.getSeriesId());
                clubObj.put("seriesName", EncodeUtils.encodeForHTML(club.getSeriesName(), sourceAgent));
                clubObj.put("totalPetrol", club.getTotalPetrol());
                clubObj.put("introduce", EncodeUtils.encodeForHTML(club.getIntroduce(), sourceAgent).replaceAll("\n", ""));
                clubObj.put("levelId", club.getLevelId());
                clubObj.put("memberSum", club.getMemberNum());
                clubObj.put("vipNum", club.getVipNum());
                clubObj.put("regionId", club.getRegionId());
                clubObj.put("url", systemConfig.getRoot() + "/" + club.getClubUrl() + "/");
                //根据clubId得到活跃度值
                ClubStat clubStat = statService.getClubStatByClubId(club.getClubId());
                if (clubStat != null) {
                    clubObj.put("rankPetrol", clubStat.getRankPetrol());
                } else {
                    clubObj.put("rankPetrol", "");
                }
                clubArr.add(clubObj);
            }
            obj.put("data", clubArr);
        }
        return obj;
    }

    /**
     * 寻找中国车友会星级排名接口 给平台那边同步数据用的 根据更新时间去查询车友会
     *
     * @param updateAt
     * @return
     */
    public List<Club> getListByUpdateAt(Date updateAt) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select club_id from ac_club where update_at >= ").appendValue(updateAt);
        sql.appendSql(" and status in ( ").appendValue(Club.STATUS_PASS);
        sql.appendSql(" , ").appendValue(Club.STATUS_LOCK);
        sql.appendSql(" , ").appendValue(Club.STATUS_SEALED);
        sql.appendSql(")");
        try {
            return geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (EmptyResultDataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 汽车网车友会入口优化开发需求 给车系频道页面调用，最多获取3条数据
     *
     * @param sid
     * @return
     */
    public List<Club> getListBySid(long sid) {
        try {
            Object[] sids = sid > 0 ? new Object[]{sid} : null;
            return pageClubByPlaceId(0, 0, 0, 0, sids, 0, null, 1, 3).getResultList();
        } catch (EmptyResultDataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据品牌ID获取车友会列表
     *
     * @param brandId
     * @return
     */
    public List<Club> getListByBrandId(long brandId, int pageNo, int pageSize) {
        try {
            if (brandId <= 0) {
                return new ArrayList<Club>();
            }
            return pageClubByPlaceId(0, 0, 0, brandId, null, 0, null, pageNo, pageSize).getResultList();
        } catch (EmptyResultDataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据省ID，市ID，品牌ID，车系ID查询车友会列表
     *
     * @param provinceId
     * @param cityId
     * @param brandId
     * @param seriesId 搜索页专用，跟一般逻辑不一样
     * @return
     */
    public List<Club> listClubByPlace(long provinceId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id =").appendValue(provinceId);
        }
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc, ac_club.total_petrol desc, ac_club.create_at  ");
        return geliDao.list(Club.class, sql.getSql(), sql.getValues());
    }

    /**
     * 搜索页省份url拼装
     *
     * @param brandId
     * @param seriesId
     * @return
     */
    public List<SearchUrl> searchProvince(long brandId, long seriesId, int themeId) {
        List<SearchUrl> list = new ArrayList<SearchUrl>();
        for (int i = 0; i < RegionUtil.provinceForSearch.length; i++) {
            SearchUrl search = new SearchUrl();
            String url = systemConfig.getRoot() + "/all/c-p" + RegionUtil.provinceIdForSearch[i];
            if (brandId > 0) {
                url += "-b" + brandId;
            }
            if (seriesId > 0) {
                url += "-c" + seriesId;
            }
            if (themeId > 0) {
                url += "-z" + themeId;
            }
            url += ".html";
            search.setUrl(url);
            search.setName(RegionUtil.provinceForSearch[i]);
            list.add(search);
        }
        return list;
    }

    /**
     * 搜索页城市url拼装
     *
     * @param brandId
     * @param seriesId
     * @return
     */
    public List<SearchUrl> searchCity(long provinceId, long cityId, long brandId, long seriesId, int themeId) {
        List<SearchUrl> list = new ArrayList<SearchUrl>();
        Map<Long, String> idMap = listCityIdByProvinceId(provinceId);
        Set<Long> set = idMap.keySet();
        Iterator<Long> it = set.iterator();
        while (it.hasNext()) {
            long sCityId = it.next();
            SearchUrl search = new SearchUrl();
            String url = systemConfig.getRoot() + "/all/c-p" + provinceId + "-s" + sCityId;
            if (brandId > 0) {
                url += "-b" + brandId;
            }
            if (seriesId > 0) {
                url += "-c" + seriesId;
            }
            if (themeId > 0) {
                url += "-z" + themeId;
            }
            url += ".html";
            if (sCityId == cityId) {
                search.setIsSelect(true);
            }
            search.setUrl(url);
            search.setName(idMap.get(sCityId));
            list.add(search);
        }
        return list;
    }

    /**
     * 搜索页品牌url拼装
     *
     * @param brandId
     * @param seriesId
     * @return
     */
    public List<SearchUrl> searchBrand(List<SearchUrl> listUrl, long provinceId, long cityId, long brandId, long seriesId, int themeId) {
        Club club = null;
        if (seriesId > 0) {
            club = this.selectMenuByClub(0, 0, seriesId);
        }
        List<SearchUrl> list = new ArrayList<SearchUrl>();
        for (SearchUrl sarchUrl : listUrl) {
            SearchUrl search = new SearchUrl();
            String url = systemConfig.getRoot() + "/all/c";
            if (provinceId > 0) {
                url += "-p" + provinceId;
            }
            if (cityId > 0) {
                url += "-s" + cityId;
            }
            url += "-b" + sarchUrl.getUrl();
            if (seriesId > 0) {
                if (club != null && club.getBrandId() == Long.parseLong(sarchUrl.getUrl())) {
                    url += "-c" + seriesId;
                }
            }
            if (themeId > 0) {
                url += "-z" + themeId;
            }
            url += ".html";
            if (brandId == Long.parseLong(sarchUrl.getUrl())) {
                search.setIsSelect(true);
            }
            search.setUrl(url);
            search.setName(sarchUrl.getName());
            list.add(search);
        }
        return list;
    }

    /**
     * 搜索页主题url拼装
     *
     * @param provinceId
     * @param cityId
     * @param brandId
     * @param seriesId
     * @return
     */
    public List<SearchUrl> searchTheme(long provinceId, long cityId, long brandId, long seriesId) {
        List<SearchUrl> list = new ArrayList<SearchUrl>();
        List<Theme> themes = themeService.listAllThemes();
        if (themes == null) {
            return list;
        }
        for (Theme theme : themes) {
            SearchUrl search = new SearchUrl();
            String url = systemConfig.getRoot() + "/all/c";
            if (provinceId > 0) {
                url += "-p" + provinceId;
            }
            if (cityId > 0) {
                url += "-s" + cityId;
            }
            if (brandId > 0) {
                url += "-b" + brandId;
            }
            if (seriesId > 0) {
                url += "-c" + seriesId;
            }
            url += "-z" + theme.getThemeId() + ".html";
            search.setUrl(url);
            search.setName(theme.getThemeName());
            list.add(search);
        }
        return list;
    }

    /**
     * 搜索页车系url拼装
     *
     * @param brandId
     * @param seriesId
     * @return
     */
    public List<SearchUrl> searchSeries(List<SearchUrl> listUrl, long provinceId, long cityId, long brandId, long seriesId, int themeId) {
        List<SearchUrl> list = new ArrayList<SearchUrl>();
        for (SearchUrl sarchUrl : listUrl) {
            SearchUrl search = new SearchUrl();
            String url = systemConfig.getRoot() + "/all/c";
            if (provinceId > 0) {
                url += "-p" + provinceId;
            }
            if (cityId > 0) {
                url += "-s" + cityId;
            }
            if (brandId > 0) {
                url += "-b" + brandId;
            }
            url += "-c" + sarchUrl.getUrl();
            if (themeId > 0) {
                url += "-z" + themeId;
            }
            url += ".html";
            if (seriesId == Long.parseLong(sarchUrl.getUrl())) {
                search.setIsSelect(true);
            }
            search.setUrl(url);
            search.setName(sarchUrl.getName());
            list.add(search);
        }
        return list;
    }

    /**
     * 查询品牌或者车系出现的次数，并按出现次数排序
     *
     * @param type 1>品牌 2>车系
     * @param i
     * @return
     */
    public List<SearchUrl> getBreadOrSeries(List<Club> list, long brandId, int type) {
        Map<Long, SearchUrl> maps = new HashMap<Long, SearchUrl>();
        List<SearchUrl> listSearch = new ArrayList<SearchUrl>();
        if (type == 1) {
            for (Club club : list) {
                if (club.getBrandId() == 0) {
                    continue;
                }
                SearchUrl searchUrl = maps.get(club.getBrandId());
                if (searchUrl == null) {
                    searchUrl = new SearchUrl();
                    searchUrl.setName(club.getBrandName());
                    searchUrl.setBrandName(club.getBrandName());
                    maps.put(club.getBrandId(), searchUrl);
                }
                searchUrl.setCount(searchUrl.getCount() + 1);
            }
        } else if (type == 2) {
            for (Club club : list) {
                if (club.getSeriesId() == 0) {
                    continue;
                }
                if (brandId <= 0) {
                    SearchUrl searchUrl = maps.get(club.getSeriesId());
                    if (searchUrl == null) {
                        searchUrl = new SearchUrl();
                        searchUrl.setName(club.getSeriesName());
                        maps.put(club.getSeriesId(), searchUrl);
                    }
                    searchUrl.setCount(searchUrl.getCount() + 1);
                } else if (club.getBrandId() == brandId) {
                    SearchUrl searchUrl = maps.get(club.getSeriesId());
                    if (searchUrl == null) {
                        searchUrl = new SearchUrl();
                        searchUrl.setName(club.getSeriesName());
                        maps.put(club.getSeriesId(), searchUrl);
                    }
                    searchUrl.setCount(searchUrl.getCount() + 1);
                }
            }
        }
        if (!maps.isEmpty()) {
            Iterator<Entry<Long, SearchUrl>> iter = maps.entrySet().iterator();
            Entry<Long, SearchUrl> entry;
            while (iter.hasNext()) {
                entry = iter.next();
                SearchUrl obj = new SearchUrl();
                obj.setUrl(entry.getKey() + "");
                SearchUrl v = entry.getValue();
                obj.setCount(v.getCount());
                obj.setName(v.getName());
                obj.setBrandName(v.getBrandName());
                listSearch.add(obj);
            }
        }
        Collections.sort(listSearch, new Comparator<SearchUrl>() {
            @Override
            public int compare(SearchUrl o1, SearchUrl o2) {
                return (int) (o2.getCount() - o1.getCount());
            }
        });
        return listSearch;
    }

    public long createClubDispose(ClubDispose clubDispose) {
        return geliDao.create(clubDispose);
    }

    /**
     *
     * @param url
     * @return
     */
    public Club selectMenuByClub(long provinceId, long brandId, long seriesId) {
        if (provinceId <= 0 && brandId <= 0 && seriesId <= 0) {
            return null;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select * from ").appendSql(geliOrm.getTableName(Club.class)).appendSql(" where 1=1 ");
        if (provinceId > 0) {
            sql.appendSql(" and province_id = ").appendValue(provinceId);
            sql.appendSql(" and province_name <>'' ");
        }
        if (brandId > 0) {
            sql.appendSql(" and brand_id = ").appendValue(brandId);
            sql.appendSql(" and brand_name <>'' ");
        }
        if (seriesId > 0) {
            sql.appendSql(" and series_id = ").appendValue(seriesId);
            sql.appendSql(" and series_name <>'' ");
        }
        return geliDao.findFirst(Club.class, sql.getSql(), sql.getValues());
    }

    public String getNameByProvinceId(long provinceId) {
        String name = "";
        for (int i = 0; i < RegionUtil.provinceIdForSearch.length; i++) {
            if (RegionUtil.provinceIdForSearch[i].equals(provinceId + "")) {
                name = RegionUtil.provinceForSearch[i];
            }
        }
        return name;
    }

    /**
     * 根据省份城市品牌车系主题等id分页查询相关的车友会
     *
     * @return
     */
    public Pager<Club> searchClubBy(long provinceId, long cityId, long brandId,
            long seriesId, int themeId, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id =").appendValue(provinceId);
        }
        if (cityId > 0) {
            sql.appendSql(" and ac_club.city_id=").appendValue(cityId);
        }
        if (brandId > 0) {
            sql.appendSql(" and ac_club.brand_id =").appendValue(brandId);
        }
        if (seriesId > 0) {
            sql.appendSql(" and ac_club.series_id = ").appendValue(seriesId);
        }
        if (themeId > 0) {
            sql.appendSql(" and ac_club.theme_id = ").appendValue(themeId);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc , ac_club.total_petrol desc, ac_club.create_at  ");
        log.info("sql: {} \n values: {}", sql.getSql(), sql.getValues());
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    private void addLog(long userId, Club c, String detailFmt, int affectedRows) {
        if (affectedRows <= 0) {
            return;
        }
        ClubDeleteLog delLog = new ClubDeleteLog();
        JSONObject obj = new JSONObject();
        obj.put("clubId", c.getClubId());
        obj.put("name", c.getName());
        obj.put("logo", c.getLogo());
        obj.put("clubUrl", c.getClubUrl());
        obj.put("createAt", c.getCreateAt());
        obj.put("createBy", c.getCreateBy());
        obj.put("adminId", c.getAdminId());
        obj.put("auditAt", c.getAuditAt());
        obj.put("auditBy", c.getAuditBy());
        obj.put("status", c.getStatus());
        obj.put("brandId", c.getBrandId());
        obj.put("seriesId", c.getSeriesId());
        obj.put("provinceId", c.getProvinceId());
        obj.put("cityId", c.getCityId());
        obj.put("qqGroup", c.getQqGroup());
        obj.put("introduce", c.getIntroduce());
        delLog.setClubInfo(obj.toJSONString());
        delLog.setCreateAt(new Date());
        delLog.setDetail(String.format(detailFmt, affectedRows));
        delLog.setUserId(userId);
        geliDao.create(delLog);
    }

    //操作5张表
    private void deleteAboutActivity(long userId, Club club) {
        //活动相关——先删除日记、内容、帖子、人员
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select activity_id from ac_activity where 1=1");
        sql.appendSql(" and club_id = ").appendValue(club.getClubId());
        List<Long> activityIdList = jdbcTemplate.query(sql.getSql(), sql.getValues(), new ResultSetExtractor<List<Long>>() {
            @Override
            public List<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<Long> activityIdList = new ArrayList();
                while (rs.next()) {
                    activityIdList.add(rs.getLong("activity_id"));
                }
                return activityIdList;
            }
        });
        if (!activityIdList.isEmpty()) {
            String[] activitySqls = new String[activityIdList.size() * 4];
            int activityIndex = 0;
            for (Long aid : activityIdList) {
                activitySqls[activityIndex++] = ("delete from ac_activity_diary where activity_id = " + aid);
                activitySqls[activityIndex++] = ("delete from ac_activity_member where activity_id = " + aid);
                activitySqls[activityIndex++] = ("delete from ac_activity_topic where activity_id = " + aid);
                activitySqls[activityIndex++] = ("delete from ac_activity_content_" + aid % 10 + " where activity_id = " + aid);
            }
            int[] activityUpdates = jdbcTemplate.batchUpdate(activitySqls);
            int countActivityDiary = 0, countActivityMember = 0, countActivityTopic = 0, countActivityContent = 0;
            for (int i = 0; i < activityUpdates.length;) {
                countActivityDiary += activityUpdates[i++];
                countActivityMember += activityUpdates[i++];
                countActivityTopic += activityUpdates[i++];
                countActivityContent += activityUpdates[i++];
            }
            addLog(userId, club, ClubDeleteLog.DEL_ACTIVITY_DIARY, countActivityDiary);
            addLog(userId, club, ClubDeleteLog.DEL_ACTIVITY_MEMBER, countActivityMember);
            addLog(userId, club, ClubDeleteLog.DEL_ACTIVITY_TOPIC, countActivityTopic);
            addLog(userId, club, ClubDeleteLog.DEL_ACTIVITY_CONTENT, countActivityContent);
        }
        //删除活动
        int[] activityUpdates = jdbcTemplate.batchUpdate(new String[]{"delete from ac_activity where club_id = " + club.getClubId()});
        addLog(userId, club, ClubDeleteLog.DEL_ACTIVITY, activityUpdates[0]);
    }

    //操作2张表
    private void deleteAboutAlbum(long userId, Club club) {
        //相册相关——先删除图片
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select album_id from ac_club_album where 1=1");
        sql.appendSql(" and club_id = ").appendValue(club.getClubId());
        List<Long> albumIdList = jdbcTemplate.query(sql.getSql(), sql.getValues(), new ResultSetExtractor<List<Long>>() {
            @Override
            public List<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<Long> albumIdList = new ArrayList();
                while (rs.next()) {
                    albumIdList.add(rs.getLong("album_id"));
                }
                return albumIdList;
            }
        });
        if (!albumIdList.isEmpty()) {
            String[] albumSqls = new String[albumIdList.size()];
            int albumIndex = 0;
            for (Long aid : albumIdList) {
                albumSqls[albumIndex++] = "delete from ac_club_album_photo_" + aid % 10 + " where album_id = " + aid;
            }
            int[] albumUpdates = jdbcTemplate.batchUpdate(albumSqls);
            int countAlbumPhoto = 0;
            for (int c : albumUpdates) {
                countAlbumPhoto += c;
            }
            addLog(userId, club, ClubDeleteLog.DEL_PHOTO, countAlbumPhoto);
        }
        //删除相册
        int[] albumUpdates = jdbcTemplate.batchUpdate(new String[]{"delete from ac_club_album where club_id =  " + club.getClubId()});
        addLog(userId, club, ClubDeleteLog.DEL_ALBUM, albumUpdates[0]);
    }

    private Map<Long, String> listCityIdByProvinceId(long provinceId) {
        final Map<Long, String> map = new HashMap<Long, String>();
        if (provinceId <= 0) {
            return map;
        }
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select distinct city_id,city_name from ").appendSql(geliOrm.getTableName(Club.class))
                .appendSql(" where  province_id=").appendValue(provinceId)
                .appendSql(" and city_id>0");
        setAllowFindStatus(sql);
        geliDao.getJdbcTemplate().query(sql.getSql(), sql.getValues(), new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                map.put(rs.getLong("city_id"), rs.getString("city_name"));
            }
        });
        return map;
    }

    private void deleteAboutMember(long userId, Club club) {
        //删除会员相关
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select user_id from ac_member_info where 1=1");
        sql.appendSql(" and club_id = ").appendValue(club.getClubId());
        List<Long> userIdList = jdbcTemplate.query(sql.getSql(), sql.getValues(), new ResultSetExtractor<List<Long>>() {
            @Override
            public List<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<Long> userIdList = new ArrayList();
                while (rs.next()) {
                    userIdList.add(rs.getLong("user_id"));
                }
                return userIdList;
            }
        });
        if (!userIdList.isEmpty()) {
            int countUpdate = 0;
            for (Long uid : userIdList) {
                User u = userService.findUser(uid);
                if (u != null && u.getJoinedClubCount() > 0) {
                    u.setJoinedClubCount(u.getJoinedClubCount() - 1);
                    countUpdate += geliDao.update(u, "joined_club_count");
                }
            }
            addLog(userId, club, ClubDeleteLog.UPDATE_JOIN_CLUB_COUNT, countUpdate);
        }
        //删除会员信息
        int[] memberUpdates = jdbcTemplate.batchUpdate(new String[]{"delete from ac_member_info where club_id = " + club.getClubId()});
        addLog(userId, club, ClubDeleteLog.DEL_MEMBER_INFO, memberUpdates[0]);
    }

    //按月分表
    private void deleteAboutMonthTable(long userId, Club club, String sqlFmt, String logFmt, int startYm) {
        //按时间分表
        List<Integer> monthList = DateUtils.getMonthList(startYm);
        String[] sqls = new String[monthList.size()];
        for (int i = 0; i < sqls.length; i++) {
            sqls[i] = String.format(sqlFmt, monthList.get(i), club.getClubId());
        }
        try {
            int[] updates = jdbcTemplate.batchUpdate(sqls);
            int count = 0;
            for (int c : updates) {
                count += c;
            }
            addLog(userId, club, logFmt, count);//打日志
        } catch (org.springframework.dao.DataAccessException d) {
            log.error("按时间分表相关操作出错了", d);//如果以后把比较旧的分表数据移走，数据库表不存在，会出错
        }
    }

    public boolean deleteClub(long userId, long clubId) {
        Club club = getClub(clubId);
        if (club == null) {
            return false;
        }
        //活动相关
        deleteAboutActivity(userId, club);
        //相册相关
        deleteAboutAlbum(userId, club);
        //按月份分表
        int startYm = 201401;
        String petrolLogSqlFmt = "delete from ac_club_petrol_log_%s where club_id = %s";
        deleteAboutMonthTable(userId, club, petrolLogSqlFmt, ClubDeleteLog.DEL_CLUB_PETROL_LOG, startYm);
        String memberPetrolLogSqlFmt = "delete from ac_member_petrol_log_%s where club_id = %s";
        deleteAboutMonthTable(userId, club, memberPetrolLogSqlFmt, ClubDeleteLog.DEL_MEMBER_PETROL_LOG, startYm);
        String memberPetrolDetailSqlFmt = "delete from ac_member_petrol_detail_%s where club_id = %s";
        deleteAboutMonthTable(userId, club, memberPetrolDetailSqlFmt, ClubDeleteLog.DEL_MEMBER_PETROL_DETAIL, startYm);
        String checkinRecordSqlFmt = "delete from ac_member_checkin_record_%s where club_id = %s";
        deleteAboutMonthTable(userId, club, checkinRecordSqlFmt, ClubDeleteLog.DEL_MEMBER_CHECKIN_RECORD, startYm);
        //删除会员信息
        deleteAboutMember(userId, club);
        //其他 表与表之间相对独立，直接根据clubId就可以删除数据
        TreeMap<String, String> otherMap = new TreeMap<String, String>();
        otherMap.put(ClubDeleteLog.DEL_AWARD_LIMIT, "delete from ac_award_limit where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB, "delete from ac_club where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_ADDRESS, "delete from ac_club_address where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_AWARD_RECORD, "delete from ac_club_award_record where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_DISPOSE, "delete from ac_club_dispose where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_FOCUS_IMG, "delete from ac_club_focus_img where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_FORUM, "delete from ac_club_forum where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_LIMIT, "delete from ac_club_limit where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_NOTICE, "delete from ac_club_notice where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_PETROL, "delete from ac_club_petrol where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_PETROL_DAILY, "delete from ac_club_petrol_daily where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_STAT, "delete from ac_club_stat where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_TOTAL_MEMBER, "delete from ac_club_total_member where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_MEMBER_APPLICATION, "delete from ac_member_application where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_MEMBER_STAT, "delete from ac_member_stat where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_MEMBER_WEEK, "delete from ac_member_week where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_STYLE, "delete from ac_club_style where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_VISITOR, "delete from ac_club_visitor where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_MEMBER_ALL, "delete from ac_member_all where club_id = " + club.getClubId());

        //按clubId分表
        otherMap.put(ClubDeleteLog.DEL_MEMBER_CHECKIN, "delete from ac_member_checkin_" + club.getClubId() % 10 + " where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_COMMENT, "delete from ac_comment_" + club.getClubId() % 10 + " where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_TOPIC, "delete from ac_topic_" + club.getClubId() % 10 + " where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_PV, "delete from ac_club_pv_" + club.getClubId() % 10 + " where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_DYNA, "delete from ac_club_dyna_" + club.getClubId() % 10 + " where club_id = " + club.getClubId());
        otherMap.put(ClubDeleteLog.DEL_CLUB_PHOTO, "delete from ac_club_photo_" + club.getClubId() % 10 + " where club_id = " + club.getClubId());
        String[] sqls = new String[otherMap.size()];
        int otherMapIndex = 0;
        for (String k : otherMap.keySet()) {
            sqls[otherMapIndex++] = otherMap.get(k);
        }
        int[] updates = jdbcTemplate.batchUpdate(sqls);//批量删除
        otherMapIndex = 0;
        for (String k : otherMap.keySet()) {
            addLog(userId, club, k, updates[otherMapIndex++]);//打日志
        }
        geliDao.delete(club, club.getClubId());//删除车友会
        return true;
    }

    /**
     * 获取所有车友会信息，不管车友会状态
     *
     * @return
     */
    public List<Club> listAllClub() {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select club_id from ac_club");
        List<Club> list = null;
        try {
            list = geliDao.list(Club.class, sql.getSql(), sql.getValues());
        } catch (DataAccessException ex) {
            log.error("Can't list all the club info!" + ex);
        }

        return list;
    }

    /**
     * 根据车系ID获取车友会列表
     *
     * @param seriesId
     * @param pagetNo
     * @param pageSize
     * @return
     */
    public List<Club> getListBySeriesId(long seriesId, int pagetNo, int pageSize) {
        try {
            Object[] seriesIds = seriesId > 0 ? new Object[]{seriesId} : null;
            return pageClubByPlaceId(0, 0, 0, 0, seriesIds, 0, null, pagetNo, pageSize).getResultList();
        } catch (EmptyResultDataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据省市ID获取车友会列表
     *
     * @param cityId
     * @param provinceId
     * @param pagetNo
     * @param pageSize
     * @return
     */
    public List<Club> getListByCityIdProvinceId(long cityId, long provinceId, int pagetNo, int pageSize) {
        try {
            return pageClubByPlaceId(cityId, 0, provinceId, 0, null, 0, null, pagetNo, pageSize).getResultList();
        } catch (EmptyResultDataAccessException e) {
            return Collections.emptyList();
        }
    }

    /**
     * 判断qq群号是否已经存在
     *
     * @param qqGroup
     * @return
     */
    public boolean isQQGroupExist(String qqGroup) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql("select count(1) from ac_club where qq_group = ");
        sql.appendValue(qqGroup);
        return geliDao.count(sql.getSql(), sql.getValues()) > 0;
    }

    /**
     *
     *
     * @param qqGroup
     * @return
     */
    public Club findClubByQQgroup(String qqGroup) {
        SqlBuilder sqlBuilder = new SqlBuilder();
        sqlBuilder.appendSql("SELECT club_id FROM ac_club WHERE qq_group = ").appendValue(qqGroup);
        List<Club> list = geliDao.list(Club.class, sqlBuilder.getSql(), sqlBuilder.getValues());
        return list.isEmpty() ? null : list.get(0);
    }

    /**
     * 根据品牌ID查找车友会，系列ID=seriesId的除外(Pager)
     *
     * @param brandId
     * @param seriesId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Club> pageClubByBrandIdExceptSeriesId(long brandId, long seriesId, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (brandId > 0) {
            sql.appendSql(" and ac_club.brand_id = ").appendValue(brandId);
        }
        sql.appendSql(" and ac_club.series_id <> ").appendValue(seriesId);
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc , ac_club.total_petrol desc, ac_club.create_at  ");
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 根据品牌ID查找车友会，系列ID=seriesId的除外(List)
     *
     * @param brandId
     * @param seriesId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public List<Club> listClubByBrandIdExceptSeriesId(long brandId, long seriesId, int pageNo, int pageSize) {
        try {
            return pageClubByBrandIdExceptSeriesId(brandId, seriesId, pageNo, pageSize).getResultList();
        } catch (EmptyResultDataAccessException exception) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据省份ID查找车友会，城市ID=cityId的除外(Pager)
     *
     * @param brandId
     * @param seriesId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Club> pageClubByProvinceIdExceptCityId(long provinceId, long cityId, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id = ").appendValue(provinceId);
        }
        sql.appendSql(" and ac_club.city_id <> ").appendValue(cityId);
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc , ac_club.total_petrol desc, ac_club.create_at  ");
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     * 根据省份ID查找车友会，城市ID=cityId的除外(List)
     *
     * @param provinceId
     * @param cityId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public List<Club> listClubByProvinceIdExceptCityId(long provinceId, long cityId, int pageNo, int pageSize) {
        try {
            return pageClubByProvinceIdExceptCityId(provinceId, cityId, pageNo, pageSize).getResultList();
        } catch (EmptyResultDataAccessException exception) {
            return Collections.emptyList();
        }
    }

    /**
     * 根据主题数组，获取相应的车友会列表
     *
     * @param themeIds
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<Club> pageClubByThemeIds(Object[] themeIds, int pageNo, int pageSize) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (themeIds != null && themeIds.length > 0) {
            sql.appendSql(" and ac_club.theme_id in ").appendValues(themeIds);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc , ac_club.total_petrol desc, ac_club.create_at  ");
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }

    /**
     *
     * @param club
     * @param xssEncode 0:不过滤，1：过滤
     * @return
     */
    public JSONObject setClub2Json(Club club, int xssEncode) {
        JSONObject jsonObject = new JSONObject();

        jsonObject.put("clubId", club.getClubId());
        jsonObject.put("clubStatus", club.getStatus());
        jsonObject.put("memberNum", club.getMemberNum());
        jsonObject.put("totalPetrol", petrolService.getValidPetrol(club.getClubId()).getTotalPetrol());
        jsonObject.put("level", club.getLevelId());
        String activityName = "";
        List<Activity> listActivity = activityService.getNewActivity(club.getClubId(), 1);
        if (listActivity != null && !listActivity.isEmpty()) {
            Activity activity = listActivity.get(0);
            activityName = activity.getTitle();
        }
        jsonObject.put("commentSum", commentService.countByClubId(club.getClubId()));
        jsonObject.put("regionRank", club.getRegionRank());
        jsonObject.put("brandRank", club.getBrandRank());
        jsonObject.put("provinceRank", club.getProvinceRank());
        jsonObject.put("rank", club.getRank());
        jsonObject.put("albumSum", albumService.countAlbumByClubId(club.getClubId()));
        jsonObject.put("photoSum", albumService.countPhotoByClubId(club.getClubId()));
        jsonObject.put("topicSum", topicService.countByClubId(club.getClubId()));
        jsonObject.put("activitySum", activityService.countActivityByClubId(club.getClubId()));
        jsonObject.put("clubUrl", club.getClubUrl());
        jsonObject.put("url", systemConfig.getRoot() + "/" + club.getClubUrl() + "/");
        jsonObject.put("brandId", club.getBrandId());
        //主题URL
        jsonObject.put("clubBackgroundImg", club.getAppBanner() == null ? "" : club.getAppBanner());
        //判断是否有升级提示
        boolean isTopLevel = clubLevelService.isHighest(club.getLevelId());
        long nextNeedPetrol = 0;
        long nextNeedVip = 0;
        if (!isTopLevel) {
            ClubLevel clubNextLevel = clubLevelService.getNextLevel(club.getLevelId());
            if (clubNextLevel != null) {
                long nextLevelId = clubNextLevel.getLevelId();
                nextNeedPetrol = clubLevelService.getNextLevelNeedPetrol(nextLevelId, (int) club.getTotalPetrol());
                nextNeedVip = clubLevelService.getNextLevelNeedMember(nextLevelId, (int) club.getVipNum());
            }
        }
        if (!isTopLevel && nextNeedVip < 1 && nextNeedPetrol < 1) {
            jsonObject.put("levelupTip", 1);//提示可升级
        } else {
            jsonObject.put("levelupTip", 0);//提示不可升级
        }
        //获取等待审核加入车友会申请数量
        try {
            List<MemberApplication> listMember4Audit = memberService.listMemberApplications4Audit(club.getClubId());
            jsonObject.put("applyCount", listMember4Audit.size());
        } catch (EmptyResultDataAccessException e) {
            log.error("无法获取待审加入车友会成员信息clubId=" + club.getClubId());
        }
        //会长昵称
        ClubMember adminClubMember = memberService.findAdminByClubId(club.getClubId());
        jsonObject.put("presidentName", userService.getNickNameByUserId(adminClubMember.getUid()));

        if (xssEncode > 0) {
            jsonObject.put("name", EncodeUtils.encodeForHTMLFilterNull(club.getName()));
            jsonObject.put("logo", EncodeUtils.encodeForHTMLFilterNull(club.getLogo()));
            jsonObject.put("levelName", EncodeUtils.encodeForHTMLFilterNull(clubLevelService.getById(club.getLevelId()).getLevelName()));
            jsonObject.put("activityName", EncodeUtils.encodeForHTMLFilterNull(activityName));
            jsonObject.put("seriesName", EncodeUtils.encodeForHTMLFilterNull(club.getSeriesName()));
            jsonObject.put("provinceName", EncodeUtils.encodeForHTMLFilterNull(club.getProvinceName()));
            jsonObject.put("cityName", EncodeUtils.encodeForHTMLFilterNull(club.getCityName()));
            jsonObject.put("introduce", EncodeUtils.encodeForHTMLFilterNull(club.getIntroduce()));
            jsonObject.put("announcement", EncodeUtils.encodeForHTMLFilterNull(this.getNotice(club.getClubId()).getContent()));
            jsonObject.put("brandName", EncodeUtils.encodeForHTMLFilterNull(club.getBrandName()));
        } else {
            jsonObject.put("name", club.getName());
            jsonObject.put("logo", club.getLogo());
            jsonObject.put("levelName", clubLevelService.getById(club.getLevelId()).getLevelName());
            jsonObject.put("activityName", activityName);
            jsonObject.put("seriesName", club.getSeriesName());
            jsonObject.put("provinceName", club.getProvinceName());
            jsonObject.put("cityName", club.getCityName());
            jsonObject.put("introduce", club.getIntroduce());
            jsonObject.put("announcement", this.getNotice(club.getClubId()).getContent());
            jsonObject.put("brandName", club.getBrandName());
        }
        return jsonObject;
    }

    /**
     * 验证申请人名字
     *
     * @param name
     * @return
     */
    public String checkRealName(String realName) {
        if (StringUtils.isBlank(realName)) {
            return "请输入姓名";
        } else if (realName.length() > 30) {
            return "姓名过长";
        } else if (!realName.matches(Regex.realName.value)) {
            return "姓名包含特殊字符";
        } else {
            return "true";
        }
    }

    /**
     * 根据车友会状态，获取用户相关车友会列表
     *
     * @param status
     * @return
     */
    public List<Club> listClubsByStatus(long userId, List<Integer> clubStatusList) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT club_id FROM ac_club WHERE 1=1 ");
        sql.appendSql(" and create_by = ").appendValue(userId);
        if (!clubStatusList.isEmpty()) {
            sql.appendSql(" and status in ").appendValues(clubStatusList.toArray());
        }
        sql.appendSql(" order by total_petrol desc, create_at");
        return geliDao.list(Club.class, sql.getSql(), sql.getValues());
    }

    /**
     * 获取发帖默认版块
     *
     * @param club
     * @return
     */
    public ClubForum getDefaultForum(long clubId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" select club_forum_id from ").appendSql(geliOrm.getTableName(ClubForum.class));
        sql.appendSql(" where  club_id = ").appendValue(clubId);
        sql.appendSql(" order by type =").appendValue(ClubForum.TYPE_SERIAL).appendSql(" desc,");
        sql.appendSql(" type =").appendValue(ClubForum.TYPE_AREA).appendSql(" desc,");
        sql.appendSql(" type =").appendValue(ClubForum.TYPE_THEME).appendSql(" desc,");
        sql.appendSql(" seq asc ");
        try {
            List<ClubForum> list = geliDao.page(ClubForum.class, sql.getSql(), 1, 1, sql.getValues());
            return (list == null || list.size() <= 0) ? null : list.get(0);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    public List<Long> getAllClubSeriesIds() {
        String sql = "select series_id FROM ac_club WHERE series_id > ? GROUP BY series_id";
        try {
            return geliDao.getJdbcTemplate().query(sql, GeliDao.longIdRowMapper, 0);
        } catch (EmptyResultDataAccessException ex) {
            log.info("not found club series");
        }
        return null;
    }

    /**
     * 判断用户是否申请、创建、已加入车友会，返回相应提示
     *
     * @param user
     * @return
     */
    public String checkJoinedClub(User user) {
        int limit = Integer.parseInt(systemConfig.getJoinClubLimit());
        String tip = "";
        int count = userService.updateJoinedClubCount(user);
        if (count >= limit) {
            tip = "您已加入其他车友会，无法创建新的车友会！";
            return tip;
        }
        count += memberService.findMemberApplication(user.getUid(), 0l, true).size();
        if (count >= limit) {
            tip = "您已申请加入其他车友会，正在审核中，无法创建新的车友会！";
            return tip;
        }
        count += this.findByFounder(user.getUid(), false).size();
        if (count >= limit) {
            tip = "您申请创建的车友会正在审核中，请耐心等待审核结果！";
            return tip;
        }
        return tip;
    }

    /**
     * 车系车友会
     *
     * @param cityId
     * @param provinceId
     * @param serisesId
     * @param pageNo
     * @param pageSize
     * @param exitCityId 此市ID除外
     * @param exitProvinceId 此省ID除外
     * @return
     */
    public Pager<Club> pageClubByPlaceId(long cityId, long provinceId, long serisesId, int pageNo, int pageSize, long exitCityId, long exitProvinceId) {
        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club.club_id FROM ac_club LEFT JOIN ac_club_stat ON ac_club.club_id = ac_club_stat.club_id where 1=1 ");
        if (cityId > 0) {
            sql.appendSql(" and ac_club.city_id =").appendValue(cityId);
        }
        if (provinceId > 0) {
            sql.appendSql(" and ac_club.province_id =").appendValue(provinceId);
        }
        if (serisesId > 0) {
            sql.appendSql(" and ac_club.series_id =").appendValue(serisesId);
        }
        if (exitCityId > 0) {
            sql.appendSql(" and ac_club.city_id <> ").appendValue(exitCityId);
        }
        if (exitProvinceId > 0) {
            sql.appendSql(" and ac_club.province_id <> ").appendValue(exitProvinceId);
        }
        //设置允许查找车友会状态
        setAllowFindStatus(sql);
        sql.appendSql(" order by ac_club_stat.rank_petrol desc , ac_club.total_petrol desc, ac_club.create_at  ");
        return pagerService.pager(Club.class, sql, pageNo, pageSize);
    }
}
